Flash Fill functionality in Excel

 

Flash Fill in Excel is one of the most powerful functions. With this single feature, one can perform most functions for which complex TEST formulas would require.

Check this video with different examples of how to use Flash Fill.

Example 1: In this example, I explain how to split name, merge name, change first name-last name, change case. Also how to extract the name from email id.

 

Example 2: GST consists of State id, PAN No, and other 3 characters. In this example, I explain how to separate PAN No from GST id, with a single click.

 

Example 3: Phone number in the US and other countries follows a specific format. In this example, I explain how to format phone numbers in various formats.

 

Example 4: Data like Account numbers, Phone numbers, etc needs to be masked for various reasons. In this example, I explain how such data can be easily masked using the flash-fill function.

 

Example 5: If there is an id like abcd123, separating text and number is a very tedious task, particularly if the length of text and number varies.

 

Example 6: Suppose you have a text and a date in 2 cells. Merging them is very difficult since the date is actually stored as a number in excel. If merged through concatenate or &, it will convert date to number first.