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:
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.
- Select the column or data you want to parse.
- 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.
- Click the Delimited option and click Next.
- 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.
- If everything looks good, click Finish.