Microsoft Excel 2013 Flash Fill Samples

Microsoft Excel 2013 Flash Fill data assistant tool enables automatic data entry based on pattern detected on sample entries by Excel 2013. If you refer to Excel tutorial What is Flash Fill in Microsoft Excel 2013? you can have detailed information on Flash Fill besides samples given in this guide.

Split String using Flash Fill

In our previous Flash Fill tutorial, we have seen how Excel 2013 Flash Fill can help you to concatenate text in cells. Now let's now work on the opposite and try to split text in a cell into pieces. Flash Fill can also help Excel users to split string in cells. Let's work on the sample data for string splitting. Enter full names of your friends in a single column on an Excel sheet as follows.

Enter the first name of the first row on a second cell. This will give the hint to split the first word from remaning text to Excel 2013 Flash Fill engine. Go to the cell below and press Ctrl+E or Data > Data Tools > Flash Fill
Now you will have all first names of full names splitted apart as seen in below sample.

You can continue to split text for the last name from full name example. Go back to the first row and in a new cell enter the lastname from the full name. In the below cell, activate Flash Fill using Ctrl+E short-cut key.

Extract Number from String using Flash Fill

An other Flash Fill example that I want to share with Excel users is extracting numbers from text stored in a cell. Assume you have dimensions of your products stored in a cell. In this example, I have weight in numbers with the measured unit together. In the first row for weight column, I extract and write manually the first value.

Then when I go to below row and press Ctrl+E for executing Flash Fill to enter following cells data automatically, you will have the following result.

Excel 2013 users can continue the Flash Fill example to fill data in units cell too. Just go to first row and type the unit of measure manually by extracting from the concatenated string value. Then in the below line, call Flash Fill to complete data entry for you.

Capitalize String with Flash Fill

An other task Excel 2013 users can use Flash Fill is to capitalize text stored in a cell. In a new cell on the same row, type the text once more but this time capitalize the first letter. And in the following row press Ctrl+E to call Flash Fill for populating remaining data automatically for the Excel user.

Here is the output after Flash Fill in Microsoft Excel 2013 is called.

Extract Zip Code from Address using Flash Fill

Since generally zip code data is found at the end of address information, Flash Fill can extract zip code or post code from address data easily. But do not forget, flash fill fetches the last number found in address text in the following example case. In fact, it does not recognize zip code data as a post code or zip code, just returns last numeric value.

If you press Ctrl+E or follow Excel menu options Data > Data Tools > Flash Fill, you will see zip code or post code stored in address data will be automatically extracted from others and displayed in the target column values.