Chapter 25: Importing and Cleaning Data
2525
See Chapter 11 for examples of formulas that extract characters from text.You should also be aware that Excel offers two nonformula methods to assist in splitting
data so that it occupies multiple columns: Text to Columns and Flash Fill.
Using Text to Columns
The Text to Columns command can parse strings into their component parts.
First, make sure the column that contains the data to be split up has enough empty col-
umns to the right to accommodate the extracted data. Then select the data to be parsed
and choose Data ➪ Data Tools ➪ Text to Columns. 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 25.12 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 characters.
Fixed Width Each component occupies the same number of characters.
FIGURE 25.12
The first dialog box in the Convert Text to Columns Wizard