The fastest way to parse text in Excel

by Jeff Davis | Aug 16, 2000 7:00:00 AM

Takeaway: If you want to parse strings such as "First Last" into separate columns, you don't need to use fancy formulas. Excel has a tool that makes the job a snap.

In “Save time by using Excel's Left, Right, and Mid string functions,” we showed you how to extract substrings from the left, right, or middle of a string with a fixed number of characters. We followed up that tip with “Using Excel's Find and Mid to extract a substring when you don't know the start point,” which lets you extract substrings from strings of varying lengths.

In this article, we’ll show you the fastest and easiest way to parse a string into separate columns. The best part is, this technique doesn’t require any formulas!

If there’s a delimiter, Excel can parse the text
Recently I noticed that a coworker was editing a spreadsheet that contained entries like the ones shown in Figure A. She had been given this raw data and asked to total and subtotal the faxes and e-mails.

Unfortunately, the numbers and the labels were combined into a single string. This poor soul was manually rekeying the number and the labels, and she had 700 rows of data.

Figure A

We’ll show you the easy way to separate the numbers from the labels in this raw data.

Fortunately, I was able to come to my coworker’s rescue by showing her how to use Excel’s Text To Columns feature to parse that text automatically. Here’s how it works.

First, select the column of cells that contains the raw data, then open the Data menu and choose Text To Columns. When you do, Excel launches the Convert Text To Columns Wizard.

Make sure the Delimited radio button is selected, and click Next. Since the delimiter in this raw data is simply the space between the number and the label, activate the check box for Space, as shown in Figure B. (Deselect Tab, which is the default selection.)

Figure B

Tell the Wizard to treat spaces as delimiters for this raw data.

You can click the Next button if you want to read the next Wizard screen. However, in cases like this one, you can simply click the Finish button. When you do, Excel will convert those labels into separate columns. The numbers on the left side of the space will be stored as values, and the strings will be copied into the next column, as shown in Figure C.

Figure C

With just a few mouse clicks, you can convert a column of labels into columns of data.

A string by any other name
This tip comes in handy when you have a column of names in the format First Last and you want to separate out the last names—just designate the space as the delimiter. The Text To Columns tool will “cut” those strings at any delimiter you specify, so you and your users need never again rekey data into separate columns.

Tales of Excel-lence
To comment on this tip (or to share your favorite Excel trick), please post a comment below or drop us a note.
Each week, Jeff Davis tells it like he sees it from the trenches of the IT battlefield. And you can get his report from the frontlines delivered straight to your e-mail front door. Subscribe to Jeff's View from Ground Zero TechMail, and you'll get a bonus of Jeff's picks for the best Web stuff, exclusively for TechMail subscribers.

18 comment(s)