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.
Excel – Copy data with comma into single column in excel
copy/pastemicrosoft-excel-2007
Related Solutions
If you've used Text to columns recently, it will apply those settings to the Paste operation. I don't remember this happening before Excel 2007, but it may have. I always have to go back and set it back to tab delimited before I start copying and pasting again.
- Paste the entire text into the first cell
- click on the icon that appears on the bottom of the paste and choose "use text import wizard"
- In step two of the wizard, choose both spaces and commas as delimiters
You should get exactly what you want. I tried this using your text and it worked. Not sure if Excel 2003 has this, but subsequent versions do.
Step 2:
Step 3:
Best Answer
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.