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.