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.

Extract numbers by replacing all other characters

The least efficient way would be to replace every non-digit character with nothing, one by one. If there are only a few unwanted characters and you type fast, that may be enough :) But this article is not about such cases. With thousands of rows and lots of different characters, this approach wastes a huge amount of time.

If you have hundreds of thousands of rows and you know that only a small share of them contain digits, it pays to find numbers in the cells first. Then you can filter the matching cells and work only with them, which is much less resource-intensive. Later you may also need to remove these numbers from text.

Extracting numbers from a cell using a formula

This formula works in Excel 2021 and Microsoft 365, which support the SEQUENCE and CONCAT functions (CONCAT is similar to JOIN in Google Sheets). CONCAT 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:

Extracting decimal numbers (with a dot or comma)

Unfortunately, the formula above doesn’t work for numbers with a decimal separator: the dot is treated as a non-digit and replaced with a space, so “1.5” turns into “1 5”.

For such cases, the formula has to be a bit more complex:

=CONCAT(IF(ISNUMBER(MID(A1,SEQUENCE(LEN(A1)),1)+0)+(MID(A1,SEQUENCE(LEN(A1)),1)="."),MID(A1,SEQUENCE(LEN(A1)),1),""))

It keeps every digit plus every dot. It assumes your decimal separator is a dot; if you use a comma, replace "." with "," in the formula. Like the previous one, it requires Excel 2021 or Microsoft 365 because of SEQUENCE and CONCAT.

Keep in mind that it also keeps dots that are not decimal separators, such as the dot at the end of a sentence. For messy text, the regular expressions approach below is more reliable.

Extract numbers with 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).

Extract numbers with 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.

Remove all characters except digits (RegEx)

RegEx to replace all non-number characters with nothing:

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

Extract the first number in a cell (RegEx)

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 decimal numbers with units of measure

To extract decimal numbers (including whole ones) together with units of measure such as “l”, “ml”, “liter” or “liters”, you can also use regular expressions with the REGEXEXTRACT function.

This is especially useful for unstructured data like product descriptions, where numeric values appear in random places. Regex lets you find such patterns precisely, even when they are surrounded by text, symbols or spaces.

For example, from “Orange juice 1.5 l” you need to extract “1.5 l”. The pattern has to account for:

• decimal numbers (0.5, 1.0, 2.75);
• an optional integer part before the dot;
• units of measure (“l”, “ml”) that may follow the number directly or after a space.

A basic pattern:

\d+\.\d+\sl

A more robust one, for different units and spellings:

\d*\.?\d+\s*(ml|l|liters?|litres?)(\s|$)

This version handles both liters and milliliters, works when there is no space between the number and the unit (“500ml”), and doesn’t produce false matches like “12 l” in “pack of 12 lemons”.

After that, you may need to separate text and numbers in the cells.

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 using Find and Replace.

Video: extracting numbers from text

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

See also:

• LEN function
• Array formulas in Excel
• MID function
• IFERROR function