Microsoft Office Tutorials and References
In Depth Information
Figure 16-8: The first dialog box in the Convert Text to Columns Wizard.
If you're working with delimited data, specify the delimiting character or characters. You'll see a preview of the
result. If you're working with fixed width data, you can modify the column breaks directly in the preview win-
When you're satisfied with the column breaks, click Next to move to Step 3. In this step, you can click a column
in the preview window and specify formatting for the column, or indicate that the column should be skipped.
Click Finish, and Excel will split the data as specified. The original data will be replaced.
Using Flash Fill
The Text to Columns Wizard works well for many types of data, but sometimes you'll encounter data that can't
be parsed by that wizard. For example, the Text to Columns Wizard is useless if you have variable width data
that doesn't have delimiters. In such a case, using the Flash Fill feature might save the day.
Flash Fill is a new feature in Excel 2013.
Flash Fill uses pattern recognition to extract data (and also concatenate data). Just enter a few examples in a
column that's adjacent to the data and then choose Data ⇒ Data Tools ⇒ Flash Fill (or press Ctrl+E). Excel ana-
lyzes the examples and attempts to fill in the remaining cells. If Excel didn't recognize the pattern you had in
mind, press Ctrl+Z, add another example or two, and try again.
Figure 16-9 shows a worksheet with some text in a single column. The goal is to extract the number from each
cell and put it into a separate cell. The Text to Columns Wizard can't do it because the space delimiters aren't