A clever addition to Excel 2013 will help split and join data automatically.
Flash Fill is a new Excel 2013 feature that’s both useful and looks good in the demonstrations you’re bound to see. It’s not on any menu in the preview but works if you know what to do.
In Excel there’s often need to join or separate strings from cells. Flash Fill copies your actions at retyping data from nearby cells and automatically does the same for matching cells.
Here’s a simple example to start with. You have two columns for First and Last name and want a column with the two elements combined.
As you type the second name (the ‘Br’ in Bruce) in Column C, Flash Fill works out that you’re trying to join the words in column A and B so it prompts with a greyed list of the rest of column C filled in. Just type Enter to confirm the Flash Fill or keeping typing to ignore it.
There’s a tooltip menu item that gives you explicit choices if you need them.
The names are filled as in the first example with the middle names ignored. But if I go back and ‘fix’ the first entry with a middle name ….
As soon as I revise the cell by adding the middle name, Flash Fill kicks in and updates the other cells to also add a middle name.
There’s other examples of this when the initial Flash Fill doesn’t properly work out that last name prefixes like ‘Al’ and ‘van’ should be included. Edit the results and Flash Fill should pickup the revised pattern and copy it down the list.
Flash Fill is a once-only deal not a dynamic listing; if you change some of the source cells then the results are NOT automatically updated.
It’s intended to help people separate out details from info that’s in some order but not fully ready for Excel to use. For example details pasted into Excel from other sources.
This is a demo from beta software so it’s not clear how this will work in the final release and we won’t talk about the many peculiarities in Flash Fill until the final release.
Microsoft is demonstrating some clever Flash Fills that extract details from long strings (eg full name) into parts (First and Last names) or even more complex details like parsing product codes. In other words the reverse of our example above.
For the moment, you might want to try out Flash Fill. Now you’ve seen it, setup some columns and give it a try.
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.