How to Stop Splitting Text to Columns When Pasting Data from Text into Excel

We often copy texts from Text into Excel and split the texts into different columns or rows by some rules via Text to Columns function. And next time you copy a series of texts, Excel will refer to the same rule to split your copied data. If we want to stop this, you can follow below article, and we will show you the method to prevent splitting texts into columns.

Stop Splitting Text to Columns


Prepare two lists of names in text, the separator is comma between each name:

How To Stop Splitting Text To Columns When Pasting Data From Text Into Excel 1

Step1: Copy the first line of names into Excel.

How To Stop Splitting Text To Columns When Pasting Data From Text Into Excel 2

Step2: Split names into five different columns by Text to Columns function:

How To Stop Splitting Text To Columns When Pasting Data From Text Into Excel 3

Step3: Now we will copy the second line of name list in Text.

How To Stop Splitting Text To Columns When Pasting Data From Text Into Excel 4

Obviously the names are split by comma automatically. You will see the Paste Options icon is also displayed.

Step4: To stop the auto split, click on the Paste Options icon, click on the arrow. The default option is Match Destination Formatting, that’s why the second line of names are auto split into columns as the first lines.

How To Stop Splitting Text To Columns When Pasting Data From Text Into Excel 5

Step5: Click on Use Test Import Wizard… option. Text Import Wizard is auto loaded for user. Just use the default option: Delimited, and click Next.

How To Stop Splitting Text To Columns When Pasting Data From Text Into Excel 6

Though ‘Delimited’ option seems to be used for splitting data by comma or other separators, it can also prevent splitting data by uncheck some options, see steps below.

Step6: On next window, uncheck all options. In Preview of selected data you can see all names are listed in one column. Click Next.

How To Stop Splitting Text To Columns When Pasting Data From Text Into Excel 7

Step7: Select Column data format, and click Finish.

How To Stop Splitting Text To Columns When Pasting Data From Text Into Excel 8

Step 8: You will find names are listed in A2 now without splitting.

How To Stop Splitting Text To Columns When Pasting Data From Text Into Excel 9

Related Posts

If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value
If Value is Greater Than A Certain Value 1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank
If Cell is Not Blank 6

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

VBA Macro For VLOOKUP From Another Sheet
vba macro for vlookup from another sheet1

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

If Cell is Blank
If Cell is Blank_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell Equals Certain Text String
If cell equals certain text_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2
If cell contains text1 or text2_1

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

If Cell Begins with One of Three Supplied Characters
If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or ...

Sidebar