Pasted Text is Inexplicably Automatically Parsed

July 06, 2002 - by Bill Jelen

Bill asks today:

I've been working in Excel. Every time that I copy data from another application and paste it into Excel, the data is inexplicably being parsed into two columns. What is going on?

You've uncovered something that is either a really cool trick (once you understand it), or the world's biggest annoyance (if you don't know what is causing it). Here is how the auto-parse works and how it gets turned on.

Open a blank worksheet. Copy these lines to cell A1 on the worksheet:

Column 1 - Column 2
Bill - Jelen
Frank - Smith
Anne - Troy

To parse that column into two columns, you would highlight A1:A4 and from the menu select Data > Text to Columns.

1. In step 1 of the wizard, indicate the data is delimited.
2. In step 2, click the Other checkbox and indicate the the data is delimited by a dash.
3. Click Finish to parse the data.

Here is the trick: Until you do another Text to Columns routine, the "-" is remembered by the Text to Columns dialog for that worksheet. You can close the worksheet, open it a month later and the Text to Columns wizard is going to remember the dash was your preferred method of parsing data.

When there is something in the other field of the Text to Columns Wizard step 2, Excel will turn on auto-parse. You can then copy data from another application (notepad, for example). When you paste to the workbook, if Excel finds any dashes, it will auto parse the data into two columns. To try it out, copy these lines to A5

Duane - Aubin
Ivana - Taylor
Scott - Pierson

To turn off the feature, you need to do another Text to Columns and uncheck the Other box in step 2.