Copy data with comma into single column in excel?

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.

C. Ross

Posted 2010-11-09T13:54:01.773

Reputation: 5 284

When I copy/paste some data in LastName, FirstName format, it is pasted into a single column by default. – Mehper C. Palavuzlar – 2010-11-09T14:02:19.673

I also get the same behavior as mentioned by Mepher. – jhamu – 2010-11-09T14:22:55.273

That depends on the regional and language settings, I guess – djeidot – 2010-11-09T14:41:51.403

@djeidot, I had just split by comma earlier, I presume that setting (and the wizard indicates it) persists to copy paste. – C. Ross – 2010-11-09T15:20:23.023

@C. Ross you are right, I have confirmed that. Check my updated answer below. – djeidot – 2010-11-09T15:30:29.270

Answers

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.

djeidot

Posted 2010-11-09T13:54:01.773

Reputation: 1 368

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.

Xantec

Posted 2010-11-09T13:54:01.773

Reputation: 2 303

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

datatoo

Posted 2010-11-09T13:54:01.773

Reputation: 3 162

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.

jhamu

Posted 2010-11-09T13:54:01.773

Reputation: 1 269

That works for one row, I have ~ 150 rows. – C. Ross – 2010-11-09T14:26:45.830

As for a tab, it only has a comma and one space, per notepad++.

– C. Ross – 2010-11-09T14:41:36.193