This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Excel Is Randomly Parsing Pasted Data
Problem: Every once in a while, I paste data from a text file to Excel, and Excel will spontaneously parse my data into several columns. I copied the names from the e-mail on the left, but when I pasted to Excel, the names appeared in one, two, or three columns. However, this may not happen tomorrow. It might happen only once every two weeks.
Figure 244 Excel parsed this data when I pasted.
Strategy: At some point during this Excel session, you used Data, Text to Columns and specified that the data was delimited by a comma. Whatever settings are left in the Step 2 dialog of the Convert Text to Columns Wizard will be applied to external data pasted to Excel for the rest of the Excel session.
To prevent this from happening, you could close Excel when you’re done working with the Convert Text to Columns Wizard. Or you could redisplay the Convert Text to Columns Wizard, go to the Step 2 dialog, and turn off the comma and tab settings. Either method will work.
On the other hand, you might want Excel to have this behavior. Perhaps you need to paste 100 documents to Excel and convert text to columns on each one. In this case, you can convert text to columns manually on the first pasted data, and the rest of the pastes will automatically be parsed using the same delimiter.