If you own Excel 2013 (or are thinking of upgrading from your current version) you may want to check out a very cool little feature called flash fill. Flash fill is a way to “teach” Excel how to create data from an existing column (or columns) of entries, with only a few keystrokes. More specifically, flash fill will automatically parse or concatenate data in your spreadsheet without having to use formulas, so it’s an especially nice tool for the novice.
Let’s say we have a column of addresses like this:
And we want to parse (separate) the addresses so that street, city, state, and ZIP code are all in their own columns. Immediately to the right of the list we type the street of the first address, and press Enter:
Now we type the first character of the next street, and Excel, having recognized a pattern in the entries, automatically suggests the rest of the street for this address as well as all subsequent addresses:
Press Enter and all the flash fill values are now in your worksheet:
You can continue on to the next column and use flash fill to obtain the city, and then continue to subsequent columns to complete the process until all address components have been extracted:
If you happen to see an error in the way the data has been parsed, you can continue to “teach” Excel by manually correcting the first occurrence of the error, and flash fill will modify subsequent entries accordingly.
You can even use flash fill to change formatting options. For example, if you use all caps for the first street value (and the first character of the second), subsequent flash fill values will also be all caps:
Concatenation works with flash fill too. You can concatenate (combine) separate address components into a standard single-line address (Street, City, State ZIP Code), although in this case it may take a few manual entries to “teach” the correct format.
A couple of tips: If you delete the values in a flash-filled column and then try to use it again in the same column, flash fill may not work reliably. Instead of just deleting values in individual cells, start afresh by deleting the whole column and trying flash fill again.
Flash fill also requires reasonably consistent formatting from address to address. Flash fill can accommodate addresses that alternate between the full state name and state abbreviation, but inconsistent use of commas, for example, will not work. For these cases you can use the CDXLocateMP function of CDXZipStream, discussed in more detail in our blog article How to Parse and Verify Address Data Using CDXZipStream.
For more information on flash fill for a variety of applications, please also see the Microsoft Excel Blog on Flash Fill.