Using Flash Fill

There have been several posts here on using functions or user defined functions (UDFs) to extract text or numbers from a longer string (most recently, here), but for many purposes it is quicker and easier to use the “flash-fill” feature, introduced in Excel 2013.

The examples below show examples of extracting a number from the end of a text string, using data copied and pasted from Strand7 as an example:

In the first example it is required to extract the numbers from the text in column B.  Simply enter the first number in the adjacent column, then press Ctrl-E (or click Data-Flash Fill):

The column is immediately filled with the required values.  A small icon appears next to the top cell, which gives the options displayed above if selected.

It is also possible to use a non-adjacent column:

Note that in this case the values extracted are not in ascending order.

More complex examples also work:

In this case the values in rows 14 to 17 (5-1 to 5-4) have been extracted as a date.  This can be fixed by simply formatting the example cell as text:

Also if the example cell is changed immediately, the flash-fill automatically updates:

There are however times when more control is required.  The screen-shot below shows results from flash-fill (in column A), compared with the ExtractNums UDF in Column D (download here).

Advertisements
This entry was posted in Excel, UDFs, VBA and tagged , , , . Bookmark the permalink.

One Response to Using Flash Fill

  1. Jim says:

    Thanks for the reminder to look into this feature. I get so used to tackling a problem the way I’ve done it before – because it worked – I neglect to look at new methods. A gentle nudge is welcomed. And the last example illustrates the need to always check results.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s