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.
4 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.
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.
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
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.
LastName, FirstNameformat, it is pasted into a single column by default.