Extract numbers from cells in Excel

How can you remove all characters from text cells except numbers—essentially extracting just the numbers from them? There are several simple and more advanced solutions.

Extracting numbers from a cell using a formula

This array formula works only in Excel builds that support the TEXTJOIN function (similar to JOIN in Google Sheets) — some builds of Excel 2016 and 2019, and all releases of Excel 2021. TEXTJOIN differs from CONCATENATE in that it can take ranges and arrays as input and return a single value.

Here’s the formula that extracts only numbers from cell A1:

=TEXTJOIN(IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0,""))

How it works:

1. Measure the length of the cell’s text value using the LEN function.
2. Create a text expression for the range of positions using concatenation with the ampersand (&).
3. Convert that text expression into an actual range with INDIRECT.
4. The ROW function returns an array of numbers from 1 to the last position in that range.
5. MID extracts the characters at each position.
6. Adding 0 to each character forces Excel to treat it as a number—numbers won’t return an error, while all other characters will.
7. IFERROR replaces errors with empty strings.
8. Finally, TEXTJOIN combines the array into a single string.

Using a custom function (UDF)

Here’s an example of a custom function (User Defined Function) that extracts numbers from text:

Function extrNum(x As String) As Long For n = 1 To Len(x) If Mid(x, n, 1) Like "#" Then extrNum = extrNum & Mid(x, n, 1) Next n End Function 

How to use it:

1. Insert the code into a new module (in the current workbook or your personal macro workbook).
2. Use it on the sheet like this: =extrNum(A1).

Using regular expressions

Regular expressions are a powerful tool for working with text data. The syntax for simple patterns is easy, but you can also create much more complex ones.

Both the free and paid versions of !SEMTools include:

• regexReplace — takes three arguments: the source text, the regex pattern, and the replacement text.
• regexExtract — takes two arguments: the source text and the regex pattern to extract.

RegEx to replace all non-number characters with nothing:

=REGEXREPLACE(A1;"[^\d]";"") 

RegEx to extract the first continuous sequence of numbers:

=REGEXEXTRACT(A1;"\d+") 

What you need:

1. Install !SEMTools.
2. Apply the functions.

Extract numbers from text in 1 click

Paid !SEMTools users can instantly extract numbers directly in place—no formulas needed.

The macro is in the “EXTRACT” group under the “Extract Characters” menu.

A useful feature is that it inserts a space between numbers if any non-number character was between them. This prevents merging separate numbers into one—important, for example, when a cell contains two product IDs.

If you don’t need this, you can simply replace the spaces with blanks.

Video tutorial

The !SEMTools algorithm works by removing all text and keeping only the numbers. Here’s a short video demo:

This post is also available in RU.