A Smart New
Feature - Flash Fill in Excel 2013
offers a very nice little tool, called flash fill, that just might
convince users to upgrade from older versions of Excel. This
tool allows you to "teach" Excel how to fill in new columns
of data based on existing column entries. It doesn't require
the use of formulas; flash fill simply uses some examples provided by
the user to figure out how the new data should look. It's
especially good for parsing (separating) or concatenating (combining)
columns of text data, such as long lists of address data.
to parse out street, city, state, and ZIP code into separate columns,
the user would input the street for the first entry immediately next
to the column containing the full addresses. After
typing the first character of the next street, Excel
recognizes a pattern in the entries and automatically suggests the
rest of the street for this address as well as all subsequent
Hit Enter and
the suggested entries will now fill the rest of the list. See
some mistakes in how the data was parsed? Correct the first
occurrence of the error and Excel will correct all subsequent entries
accordingly. You can then continue on to the next column and
repeat the process for city, state, and ZIP code. This also
works for concatenation, and if you want to change the case to all
capitals, flash fill will do that too.
For a flash
fill tutorial, please see this short Microsoft training video.
requires reasonably consistent formatting from address to address.
Inconsistent use of commas, for example, will not work with flash
fill. For these cases you can use the CDXLocateMP function
of CDXZipStream, or our subscription-based service CDXStreamer.
Please refer to the blog post Address Verification and Correction in
CDXZipStream and CDXStreamer for more information.