Microsoft Office Tutorials and References
In Depth Information
Filling cells and creating data series
Automatic parsing and concatenation using Flash Fill
New in 2013, Flash Fill might be the smartest command on the Fill menu. It does tricks that
Microsoft refers to as “splitting columns of data” (also known as parsing ) and “combining
text from two or more cells into one cell” (also known as concatenating ). Flash Fill can also
be used to change the case of text.
One of the great things about Flash Fill is that often, you don’t even need to click anything;
it just happens. For example, the worksheet in Figure 8-32 contains staff names split into
separate cells, but suppose you also need full names together in one cell. One full name
was typed into column C, and then after typing the first letter of the next name in the
second row, Flash Fill displayed what it deduced were the desired concatenated results—in this
case, the correct ones.
Figure 8-32 Flash Fill guesses what you want to concatenate and displays its suggestions for the
rest of the column in gray.
You’ll find the StaffList.xlsx workbook with the other examples on the companion website.
With the gray Flash Fill entries displayed as shown in Figure 8-32, pressing Enter is all you
need to do to finish the action and ill the suggested entries down the column. You can
also add text to be inserted in each entry, such as adding a comma to create a column of
last-name-first entries such as “Nadov, Yinon.” Or for example, Figure 8-33 shows the same
worksheet with an email address entered in cell C2, which was then re-selected. Then on
the Home tab, we clicked the Fill menu in the Editing group, and then clicked Flash Fill,
which used the example entry to extrapolate the rest of the entries in the column, as shown
in Figure 8-34.