Microsoft Office Tutorials and References
In Depth Information
Figure 16-7: The imported data was put in one column, rather than multiple columns.
If the text is all the same length (as in the example), you might be able to write a series of formulas that extract
the information to separate columns. The LEFT, RIGHT, and MID functions are useful for this task (see
Chapter 5 for examples).
You should also be aware that Excel offers two nonformula methods to assist in splitting data so it occupies
multiple columns: Text to Columns and Flash Fill.
Using Text to Columns
The Text to Columns command is a handy tool that can parse strings into their component parts.
First, make sure that the column that contains the data to be split up has enough empty columns to the right to
accommodate the extracted data. Then select the data to be parsed and choose Data ⇒ Data Tools ⇒ Text to
Excel displays the Convert Text to Columns Wizard, which consists of a series of dialog boxes that walk you
through the steps to convert a single column of data into multiple columns. Figure 16-8 shows the initial step, in
which you choose the type of data:
• Delimited: The data to be split is separated by delimiters such as commas, spaces, slashes, or other charac-
• Fixed Width: Each component occupies exactly the same number of characters.
Make your choice and click Next to move on to step 2, which depends on the choice you made in Step 1.