Excel Is Randomly Parsing Pasted Data

December 16, 2021 - by Bill Jelen

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.

You've selected some data from Notepad and pasted to Excel. Rather than each item appearing in column A, Excel has automatically split the data at each comma. This is not how Excel would normally do the paste. — Figure 230. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Joshua Brown on Unsplash