How can I copy and paste multiple rows of data that contains a comma, such as LastName, FirstName into a single column in Excel 2007? The default behavior seems to be to split the data into two columns.

  • When I copy/paste some data in LastName, FirstName format, it is pasted into a single column by default. – Mehper C. Palavuzlar Nov 9 '10 at 14:02
  • I also get the same behavior as mentioned by Mepher. – jhamu Nov 9 '10 at 14:22
  • That depends on the regional and language settings, I guess – djeidot Nov 9 '10 at 14:41
  • @djeidot, I had just split by comma earlier, I presume that setting (and the wizard indicates it) persists to copy paste. – C. Ross Nov 9 '10 at 15:20
  • @C. Ross you are right, I have confirmed that. Check my updated answer below. – djeidot Nov 9 '10 at 15:30

Use the text importation wizard instead of pasting the data (Data -> Obtain External Data -> From text)

Edit: I found out that, after using the Text to Columns wizard with the comma option enabled, the results persist to copy-paste, as C. Ross pointed out. In this case, launch the assistant again, select the Delimited type and uncheck Comma. This will prevent text from being split by comma in the future.

| improve this answer | |

When you paste the data into Excel there should be a small icon location at the bottom of the window and to the right of the data which will look like a clip board. if you click on that you should be able to choose Use Text Import Wizard. In the window that opens choose Fixed Width and press next. on the next screen make sure there are no break lines inserted and then click Finish.

| improve this answer | |

Also, if you have ever opened a txt, or csv file previously in excel and set the delimiter to commas, it will try to help you for future file openings.

Try, changing the import settings by opening a txt file, no matter what it is, and set the files import delimiters as you expect to paste them. Then on the next attempt it should get it right

| improve this answer | |

If you have only one row of data, try doing it by pressing "F2" before pasting the data.

Also check if your data does not contain "tab" in between.

| improve this answer | |
  • That works for one row, I have ~ 150 rows. – C. Ross Nov 9 '10 at 14:26
  • As for a tab, it only has a comma and one space, per notepad++. – C. Ross Nov 9 '10 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.