Pinterest Pixel

Extract Numbers Using Flash Fill In Excel

Bryan
 .
Extract Numbers Using Flash Fill In Excel | MyExcelOnline Extract Numbers Using Flash Fill In Excel | MyExcelOnline

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.

Extract Numbers Using Flash Fill In Excel | MyExcelOnline

download excel workbook Flash-Fill-Extract-Numbers.xlsx

 

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

Extract Numbers Using Flash Fill In Excel

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:

Extract Numbers Using Flash Fill In Excel

 

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. 

Extract Numbers Using Flash Fill In Excel

 

*** 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

Extract Numbers Using Flash Fill In Excel

 

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!

Extract Numbers Using Flash Fill In Excel

 

 

Add Hyphens To Serial Numbers Using Excel Flash Fill

 

If you like this Excel tip, please share it
Extract Numbers Using Flash Fill In Excel | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Show Averages With an Excel Pivot Table

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...