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:

=TRIM(CONCAT(IFERROR(MID(A1;SEQUENCE(LEN(A1));1)+0;" ")))

How it works:

Using a custom function (UDF)

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

Function extrNum(x$)
For i=1To Len(x):c=Mid(x,i,1):extrNum=extrNum & IIf(c Like "#",c," "):Next
extrNum=WorksheetFunction.Trim(extrNum)
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.

Those using Google Spreadsheets or the latest Excel 365 with updates, might already benefit from using regular expressions. But those who don’t have inbuilt functions for regular expressions, will find this section useful.

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. Use regEx functions for free!

Extract numbers from text in 2 clicks

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 by default. 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: