Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Contact
Home

 

 

Past Tip of the Day

 

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.
In step 1 of the wizard, indicate the data is delimited.
In step 2, click the Other checkbox and indicate the the data is delimited by a dash.
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.


By Bill Jelen on 06-Jul-2002

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

 

Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.