How to Extract Numbers from Text in Excel (Free Macro)

Four ways to extract numbers from text in Excel

To extract numbers from text in Excel, you have four options that actually work: Flash Fill for simple patterns, an array formula, Power Query's split feature, or a free macro that pulls every number into the next column. Excel has no single "get the numbers out" button, which is exactly why this question comes up so often.

The right choice depends on how messy your data is and how often you'll do it again. Here's the quick version before we get into each one.

Method	Best for	Keeps decimals?	One click?
Flash Fill	Simple, consistent patterns	Only if you type it	No, needs an example
Array formula	A one-off in any Excel version	No, it mashes digits	No
Power Query	Repeatable data pipelines	Yes, as split columns	No, needs setup
Free macro	Messy, recurring, multi-number cells	Yes	Yes

TL;DR: Key takeaways

• Excel has no built-in command that extracts numbers from text, so you pick a method based on your data and how often you repeat it.
• Flash Fill (Ctrl + E) is fastest when every cell follows the same pattern. It falls apart on irregular text.
• The array formula works everywhere but jams every digit together, so Order 48213 ref 9 becomes 482139.
• A free macro handles the messy real-world case: multiple numbers per cell, kept separate and comma-separated, in one click.
• Don't use the number-extractor on currency like $1,234.56. Use Convert Text to Numbers instead, for reasons covered below.

Method 1: Flash Fill (fastest when the pattern is clean)

Flash Fill watches what you type and continues the pattern for the rest of the column. It's the fastest option when your text is consistent.

1. In the cell next to your first row, type the number you want out of it. If A2 is Invoice INV-4821, type 4821 in B2.
2. Press Ctrl + E (or Data > Flash Fill).
3. Excel fills the rest of the column by copying your pattern.

Flash Fill is pattern-matching, not real parsing. It shines when the number sits in the same spot every row. Microsoft's own guide to Flash Fill shows more examples.

The catch: give it irregular text (a number at the start of one row, the end of another, two numbers in a third) and it guesses wrong. You end up correcting cells, which defeats the point.

Method 2: The array formula (works everywhere, reads terribly)

If you want a formula that runs in any Excel version, this is the classic:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1,""))

In Excel 2019 and earlier, enter it with Ctrl + Shift + Enter. It walks every character, keeps the digits, and joins them.

It works, but read what it actually does. It concatenates every digit in the cell, so Order 48213, backup ref 9 comes out as 482139. It drops the decimal point, so Weight 2.5kg becomes 25. For a deeper formula breakdown, Exceljet's extract-numbers formula is the reference.

On Excel 365 you can shorten it with REGEXREPLACE(A2,"[^0-9]",""), but that still fuses digits and loses decimals. Formulas are fine for a single clean column. They fight you the moment a cell holds more than one number.

Method 3: Power Query (best for pipelines you rebuild)

Power Query is the right tool when this is a step you repeat on every new export.

1. Select your column and choose Data > From Table/Range.
2. In the editor, pick your text column.
3. Go to Home > Split Column > By Digit to Non-Digit.

Excel splits the text wherever a digit meets a letter, dropping the numbers into their own columns. Microsoft documents the full feature under split a column of text in Power Query.

The upside is that the steps are saved and re-run on next month's file. The downside is real setup time and a learning curve. For a one-time cleanup, it's a lot of ceremony.

Method 4: The free one-click macro (built for messy data)

When your cells are inconsistent, or a single cell holds several numbers you need to keep apart, a macro is the cleanest answer. Download Extract Numbers From Text. It's a free .xlsm file with one macro. No install, no signup, and it runs offline so your data never leaves your machine.

First time running one of our macros? See How to run our tools for the 30-second setup.

1. Select a single column of text cells.
2. Press Alt + F8, pick ExtractNumbersFromText, click Run.
3. If the column to the right already has data, the macro asks before overwriting it.
4. Every number lands in the cell to the right. If one cell has several numbers, they come out comma-separated, like 4821, 9.

The macro keeps decimal points, skips empty cells, and skips cells that contain a formula so it never clobbers your calculations. When it finishes, it reports how many cells it scanned and how many produced numbers.

Example: order numbers buried in email subjects

Here's where the macro earns its place. A support analyst at an online homeware store exports 3,000 rows of email subjects into Excel for a returns report. The subjects read like Re: Order 48213 shipping delay and RMA 5567 // Order 48990. The order numbers need to be in their own column.

The manual route is copy, find the digits, paste, repeat 3,000 times, which is a lost afternoon. The array formula stumbles because some subjects carry two numbers, an RMA and an order, and the formula fuses them into one meaningless string.

The analyst selects the subject column, runs the macro, and gets a clean column of numbers next door. Rows with two numbers show both, comma-separated, so the RMA and the order stay readable. A job that used to eat an afternoon finishes in about 10 seconds, and the report ships before lunch.

When the macro is the wrong tool

Two honest limits, because picking the right tool matters more than forcing one.

Currency with thousands separators. The extractor treats the comma in $1,234.56 as a divider between numbers, so you'd get 1, 234.56. That's not what you want for money. Reach for the Convert Text to Numbers macro instead, which understands currency symbols and turns the text into real, sum-friendly numbers.

Negative signs. The macro pulls digits and decimals, not the leading minus. A value of -45 comes out as 45. If sign matters, clean the numbers first, then convert.

For related parsing jobs, pull email addresses out of a text column or split a cell on a delimiter cover the neighboring cases. All three sit inside the broader guide to cleaning up data in Excel.

FAQ

How do I extract only the numbers from a cell in Excel?

Type the number into the next cell and press Ctrl + E for Flash Fill when the pattern is simple. For messy or multi-number cells, run the free Extract Numbers From Text macro, which writes every number from a cell into the column to its right in one click.

How do I get the numbers from the end of a text string?

Flash Fill handles it if the number is always at the end: type the result once and press Ctrl + E. The macro is safer when the position varies row to row, because it scans the whole cell rather than assuming the number sits in a fixed spot.

How do I remove the text and keep only the numbers?

Run the macro on the column, then delete or hide the original text column. The macro leaves your source cells untouched and writes the extracted numbers alongside them, so you can check the result before removing anything.

Does this work on Excel for the web or Mac?

The macro runs on Excel for Windows and Mac, version 2016 and later. It does not run in Excel for the web, which has no VBA. On the web, use Flash Fill or Power Query instead.

What happens to decimals and currency amounts?

Decimal points are kept, so 2.5 stays 2.5. Currency is the exception: a thousands comma like $1,234.56 gets split into 1, 234.56. For money columns, use Convert Text to Numbers rather than the extractor.

What to do next

Pick the method that matches your data. Flash Fill for clean patterns, Power Query for pipelines you rebuild, and the macro for the messy, multi-number cells that break formulas.

Download the free Extract Numbers From Text macro and run it on your own data. It's the fastest way to extract numbers from text in Excel when the cells are anything less than perfect.