Flash Fill is one of Excel 2013's most useful features. It isn't possible to overstate how useful this tool is. I find myself using Flash Fill almost every day, to solve a huge number of different problems.
This lesson gives examples of many everyday tasks, that I've found can be quickly and simply completed using Flash Fill. I've also included all of the examples in the sample file: Flash Fill Examples.
Let's begin by splitting text. In the first example, I want to extract Jessica from the name Jessica Elizabeth Sagan. So I type: Jessica, into the First Name Box.
For all of these examples I'll use the Shortcut key Control+E instead of Home on the Ribbon, Editing group, Fill, and Flash Fill. Because as you can see, the shortcut key Control+E will do exactly the same thing, and it will be a little faster. So let's see if Flash Fill can extract all of the first names by pressing Control+E, and of course it can.
And for the last name we have Sagan, once again Control+E extracts all of the last names.
Now let's look at concatenation. I want Jessica, Elizabeth, Sagan to be concatenated into Jessica Elizabeth Sagan. I press Control+E and, even though Steven Bell doesn't have a Middle Name, Flash Fill has figured out exactly what I wanted.
Now let's look at adding commas. This is a very useful name format for alphabetical sorting. I'd like Jessica Elizabeth Sagan to appear as Sagan, Jessica. I'll press Control+E, and the Last names are now shown at the beginning of the name.
Now let's look at concatenating text and adding extra text at the same time. I want the Full Name to appear as: "First Name: Jessica Last Name: Sagan" And Control+E Flash Fills exactly the same formatting into the other two cells.
Now let's look at removing titles from names. The full name is: Miss Jessica Elizabeth Sagan and I'd want this to simply be: Jessica Sagan. Control+E, and Flash Fill can do that just fine.
Now let's look at extracting initials from names. In the case of Jessica Elizabeth Sagan, the initials are: JES. When I press Control+E, you can see that Flash Fill has figured out that in the case of Steven Bell, I'd simply want SB, because he doesn't have a middle name.
Now let's look at using Flash Fill to manipulate dates. I want to extract the day (which is 19 in this case), from the date: 19th of January 2013. Control+E, and I've also extracted the day from the other dates.
Let's now extract the Month. The Month is January, (for the first date). Control+E, and you can see that I've also extracted August and September.
Now let's look at the Year. In the first example, the Year is 2013. Control+E, and I've also extracted 1967 and 1999.
Now let's extract the day and the month from a date. 19th of January, 2013, will change to simply: 19th January. Control+E, and that's worked fine.
Now let's look at some more examples: extracting a domain name from an email address. Mary@Quitecontrary.com; we want to extract: QuiteContrary.com. I press Control+E, and both Dumpty.com and Nimble.com have also been extracted.
Let's look now at title case: jessica elizabeth sagan is all lower case. I'll change this to Jessica Elizabeth Sagan. Control+E, and all of the names have been restored to title case.
Now let's look at some USA telephone numbers. In the case of Books A Million, the correct formatting is: (113) 496-0227. If I now press Control+E, the other telephone numbers are formatted correctly.
And that concludes Lesson 2-21: Use Flash Fill to Solve Common Problems.