MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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. day, late in the afternoon, the pasted data starts automatically parsing into separate columns!

Data Pasted to Excel Starts Splitting at Comma?
Data Pasted to Excel Starts Splitting at Comma?

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.

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.