Software

A formula-less technique for parsing Excel data

Excel's Text to Columns feature parses data quickly and almost effortlessly.
Whether you're supporting a legacy application or importing foreign data, you probably have to parse entries occasionally. For instance, you might have to parsing first and last names. You could use a complex formula to separate the names, but depending on the condition of the data, one formula might not be enough. I like to use the easiest method possible, which in this case, is the Text to Columns feature. To use Text to Columns to parse existing data, do the following:

  1. Select the column or data you want to parse.
  2. Choose Text to Columns from the Data menu. In Excel 2007, click the Data tab and choose Text To Columns in the Data Tools group.
  3. Click the Delimited option and click Next.
  4. Click the Space option in the Delimiters section. Now, this won't always be the case, but a single space character usually separates first and last names. Take a minute to view the results in the Data Preview pane. If you don't get the expected results, try another delimiter.
  5. If everything looks good, click Finish.

It's a one-time deal, so if you have to clean up data on a regular basis, a formula or even a function might be a more efficient solution.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks