Flash Fill in Excel is a new feature that was introduced in Excel 2013.  Flash Fill allows you to combine, extract, move & transform data that belongs in one column, into a new column.

One of the cool uses of Flash Fill is extracting the numbers from your text automatically.

DOWNLOAD EXCEL WORKBOOK

 

To demonstrate the power of Excel’s Flash Fill, we will start off with the following table of data:

Flash Fill Numbers 01

Our mission is to extract the numbers from within the text cells.  We can use a VBA macro or complex formulas (which will take time to figure out and implement) or simply use Flash Fill…

 

STEP 1: Type 803.45 as the first entry in the Extracted Number column:

Flash Fill Numbers 02

 

STEP 2: In the second entry, once you type the first number 4 of 47512Excel will auto-suggest to Flash Fill the rest of the numbers.

Excel is smart enough to infer that you are trying to extract the numbers in the text.

If the Flash Fill looks good, press Enter. 

Flash Fill Numbers 03

 

*** IMPORTANT: If Flash Fill doesnt’ start automatically in your selected cell when you type in data that matches a pattern, you will need to start Flash Fill manually.

This is done by clicking on Data > Flash Fill or by pressing the Flash Fill keyboard shortcut CTRL+E

flash fill excel 2013

 

What is very impressive is regardless of the location of the number i.e. Beginning, middle or the end, Excel was able to extract this number without the use of a single formula or VBA!

Flash Fill Numbers 04

Flash Fill - Extract Numbers

 

HELPFUL RESOURCE:

oztraining_728x90

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

How To Make Negative Red Numbers In Excel When you are working with lots of different numbers in Excel, you sometimes want your numbers to stand out by showing them in a negative red number with a minus sign in the front.To do this you need to select your numbers and press CTRL+1 to bring up the Format dialogue box. ...
Use Excel Goal Seek To Find The Formula Result You... If you have a formula and want to show a specific result, but you do not know what input values to change within the formula, then Excel's Goal Seek feature is the one for you.Imagine you are calculating the payment terms on a loan.Your PMT formula gives you an amount of ...
Go To Blanks The Go To Special tool within Excel is a must for any serious Excel user as it has an array of useful spreadsheet formatting and clean up tools.One that I use all the time is the Go To Special > Blanks.  This allows you to delete multiple blank rows/column within seconds. ...
Find & Replace The Find & Replace feature or CTRL+H shortcut allows you to amend your data in seconds.  Imagine you had thousands of rows of data that was downloaded from an external system with the wrong date.  A simple CTRL+H will save you heaps of time!  See how below.DOWNLOAD WORKBO...