Part IV: Managing and Analyzing Data
Make your choice and click Next to move on to step 2, which depends on the choice you
made in step 1.
If you’re working with delimited data, specify the delimiting character. You’ll see a preview
of the result. If you’re working with fixed-width data, specify the column breaks directly in
the preview window.
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. For
example, if you have data that looks like a number but is really text, you can format the
column as Text so that you preserve any leading zeros. Click Finish, and Excel splits the
data as specified.
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, the Flash Fill feature might save the day. But keep in mind that Flash Fill works suc-
cessfully only when the data is consistent.
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 choose Data ➪ Data Tools ➪
Flash Fill (or press Ctrl+E). Excel analyzes the examples and attempts to fill in the remain-
ing cells. If Excel didn’t recognize the pattern you had in mind, press Ctrl+Z, add another
example or two, and try again.
Figure 25.13 shows a worksheet with some text in a single column. The goal is to extract
the numeric value from each text string and put the number into a separate cell. The Text
to Columns Wizard can’t do it because the space delimiters aren’t consistent. It might be
possible to write an array formula, but it would be complicated.
This workbook, which also includes other Flash Fill examples, is available on this book’s website at
http://www.wiley.com/go/excel2019bible. The filename is flash fill demo.xlsx.
To try using Flash Fill, activate cell B1 and type the first number ( 20 ). Move to B2, and
type the second number ( 6 ). Can Flash Fill identify the remaining numbers and fill them
in? Choose Data ➪ Data Tools ➪ Flash Fill (or press Ctrl+E), and Excel fills in the remaining
cells in a flash. Figure 25.14 shows the result.
