Flash Fill was introduced in 2013, but I have clients who are just upgrading to 2013 this year. This is the kind of feature that makes upgrading worthwhile.
Flash Fill is a new tool introduced in Excel 2013. Its a simple tool to handle a frequent problem. You have a small set of data and you need to break it apart into separate columns or join separate columns of data together.
Previously, I would have handled it with a series of text functions in Excel (LEFT, RIGHT, MID) combined with FIND and LEN if the data was complex enough. But if the data set is small, writing a formula sometimes seems like an overly complicated answer to a simple problem (why not just retype?).
Now Flash Fill is stepping in to help you handle this problem. If you give it a series of data (column orientation only) and an example of the pattern you want to extract, it will extract the data for you.
You can see once the second name is typed in the column adjacent to the list of full names, Flash Fill is able to see the pattern and offer all the first names in the list. Pressing enter autocompletes the action and the names are filled in. To do this, there can not be more than two blank columns between the source data and the resulting column. You can use the Ctrl + e shortcut to start flash fill.
You can click the Flash Fill icon to display the menu, accepting the suggestions will have all the names autocomplete.
Here is an even trickier scenario, in the list above some names have two middle initials. Using the “default” flash fill means only the second initial will display in those names. However if I return to any of the names on the list (with two initials) and correct the example to two initials, all of the two initials examples will be extracted.
I find that seriously impressive.
I can split data in a cell into multiple columns and I can also use Flash Fill to join multiple columns of data together.
The same technique used above. Note that I’ve been able to add commas and periods to the text as well.
In the same way, I can use Flash Fill to apply formatting, in this case putting a space between the first and second part of a postal code. You can also use it to format telephone numbers and date information.
I was (and still am) excited to share Flash Fill with my favourite clients. If you are interested in becoming one of my clients, drop me a line at catharine@mytechgenie.ca