Excel: Flash Fill

Flash Fill is a tool that is similar to AutoFill and Text to columns.

In contrast to AutoFill, Flash Fill is not restricted to a single row or column, but it also looks at the surrounding cells to find more complicated patterns.

It differs from the Text to columns tool in that, it can extract numbers that are separated by different delimiters and can also be located in different positions inside the cell. 

You can find Flash Fill in three different places: 

  1. HOME >> Editing >> Fill >> Flash Fill,
  2. DATA >> Data Tools >> Flash Fill,
  3. Ctrl + E keyboard shortcut. 

Example 1:

Let's use the following example.

 

Suppose that you want to extract the number from each cell and insert it into the cell in the right column. Not all of them are separated by space (e.g. $399) and they are located in different places. This is a perfect example to demonstrate how the Flash Fill works. 

Enter number 5 in cell C2 and press Ctrl + Enter to stay inside the cell. Use Flash Fill to fill other cells.

Notice that the data inside cells C5, C7, C9, C11 doesn’t contain decimal fractions but integers. Now, change the value 141593 in cell C5 to value 3.141593.

 

Look that the Flash Fill tool „learns” from your corrections, so it is very important for the data to be consistent.

Download attachment