Excel Flash Fill: 15 Things You Can Do In a Flash. Part 1

“Excel Flash Fill Is A Brilliant Time Saver,” writes Life hacker Australia. “In fact, it’s so good it feels like magic,” says Tech Radar

“Flash Fill brings the power of text manipulation to the hands of common, non-technical, everyday users. Even people who aren’t familiar with Excel can use the feature,” says Chad Rothschiller, a program manager with the Excel 2013.

CNN Money calls Excel Flash Fill as “Excel 2013’s coolest new feature that should have been available years ago”

So, What is this Flash fill? Where did it come from? How can it make excel-dummies work like Pros? Is it going to eliminate formulas & Macros?

Excel Flash Fill is a Programming by Example (PBE) data manipulation tool that is applying machine learning to binary decision diagrams. Flash fill automates repetitive string transformations using examples. Once the user performs one/two instances of the desired transformation, flash fill learns from these and replicates the transformation in all other rows.

Its origin was a simple act of kindness when Sumit Gulwani, a senior researcher at Microsoft attempted to help a business lady to merge cells but he couldn’t due to his lack of excel skills.  For 4 years he embarked on collaborative research in an attempt to bring machine learning and programming to the masses of non-tech users–Excel Flash fill was born.

How do you access Flash fill?

Please note Flash fill is ONLY available in Excel 2013 and later.

Ctrl +  E is the keyboard shortcut or Go to Data Under Data Tools

This article shows you the diverse ways that Flash fill can save you time and make you look like a Pro.

1. Merging text
2. Splitting text
3. Complex extraction of Text strings
4. Formatting Numbers
5. Extracting numbers in String
6. Convert Dates to ISO format
7. Swapping Day and Month Values in a Date
8. Insert Carriage Return Data
9. Remove Carriage Return Data
10. Trim Leading Spaces
11. Change case (Proper, Upper, Lower, Sentence)
12. Replace Cell contents
13. Insert Text
14. Generate Abbreviations (KTN, IBM, KFC)
15.  Custom format any text.

1.Merging Text

This is a 3 Simple Step process. No more writing concatenation formulas or Macros

1.  Write an example of the desired outcome. NB: Write more than 1 example for better results.
2. Press Enter
3. Press Ctrl+E

2.Splitting Text String

Sometimes Flash Fill requires more than 1 example to “read your Mind”.

This requires a 2nd or a 3rd example as shown below.

Assume you have a combined description and you desire to split it into respective columns.

With Excel flash fill, you no longer need to know  complex string manipulation formula like below

=MID(A2, SEARCH(" ",A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2) + 1) - SEARCH(" ", A2) -1)

NB: For Flash Fill to work with dates, Ensure you format cells in the date column to “dd/mm/yyyy”

 3.Complex Extraction of Text String

The more complex the string manipulation the more examples that excel flash fill requires.

Previously, to extract the substring between 1st and 2nd hyphens  shown above, you will be required to write below formula

=MID(A2, 
FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),1))+1, 
FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),2)) - FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),1))-1
)

Excel Flash Fill saves time and headache but you will need a few examples to teach it your required outcome.

4.Formatting Numbers

Excel Flash fill can also be used to custom format numbers from simple to complex.

For example, if you want to store below telephone numbers by adding a preceding zero, grouping 1st 3 numbers using a bracket and separating the other two groups using a hyphen.

Either Use below formula

="(0"&RIGHT(A3,3)&")"&"-"&(MID(A3,4,3))&"-"&LEFT(A3,3)

Or Just show Excel Flash fill one desired example

5.Extracting Numbers in a String Using Excel flash fill

Extracting numbers in a string can be very sophisticated and require advanced excel programming skills.

Excel flash fill makes extracting numbers look like child play.

For example, to extract numbers in the below example, you need this formula;

=IF(
SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0,
 SUMPRODUCT(
MID(0&A2, 
    LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))*ROW(INDIRECT("$1:$"&LEN(A2))),0),
        ROW(INDIRECT("$1:$"&LEN(A2))))+1,1)*
         10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),
""
)

NB: If you have Leading Zeros, ensure you format the extracting column as text.

6.Convert Dates to ISO format

7.Swapping Date & Month Values in a Date

8.Insert Carriage Return Data using excel flash fill

Generating a list that has carriage return like postal addresses requires a very complex formula or a Macro.

If you go the formula way, there is a likelihood you may slow down your spreadsheet.

See below the Flash Fill Magic that any Excel dummy can execute.

NB: To avoid the article over-flowing, other 7 uses will be addressed in Part 2

Cons of Using Flash Fills

It is not all rosy with Flash Fills as it has a number of weaknesses;

1. Unlike Formulas, Its results are static. When original data changes results remain the same
2. Sometimes they convert numbers to a string.
3. In case of inconsistent data, it may fail to identify a pattern no matter the number of examples given
4.  Ignores cells with non-printable characters

Conclusion:

99% of computer users do not know programming and struggle with repetitive tasks.

This should no longer be the case! Let Excel flash fill do the heavy lifting

Excel Flash fill brings to every excel user ability to use machine learning and programming.

DOWNLOAD WORKSHEET

More resources

Excel 2013 Flash Fill: 23 Amazing Examples

More info on Programming By Example

Using Flash Fill to Clean Data

Flash Fill: Text wrangling for non-programmers

MORE ARTICLES

FIND THE LAST OR NTH OCCURRENCE IN EXCEL USING MULTIPLE CRITERIA

VLOOKUP IS DEAD–OR IS IT?