Data Pasted to Excel Starts Splitting at Comma?

September 11, 2014 - by Bill Jelen

Usually, you grab text from Notepad or Email or a web page, paste to Excel, and it all stays in one column.

But...one day, late in the afternoon, the pasted data starts automatically parsing into separate columns!

Here is the deal... I bet you did a Text to Columns earlier today, where you split the data by a Comma or a Space.

Normally, Step 2 has a checkmark next to Tab. Not many people paste data with tabs, so you never noticed that Excel *always* splits pasted data by whatever is in step 2 of the Text to Columns Wizard. But, on a day where you do a text to columns and choose Comma or Space or Semi-colon.... well, on those days, any data copied from an external program and pasted to Excel will automatically parse.

To "fix" it: Close and re-open Excel. Or do a fake text to columns and change back to a Tab.

Also... every once in a while you might have to do a lot of pasting and then text to columns. On those days, doing one fake Text to Columns to get step 2 to have a comma can save you a lot of time!

This is one of the tips in Learn Excel 2007-2010 from MrExcel – 512 Excel Mysteries Solved.