How to Extract Only Numbers from an Excel Cell Using Formulas?

Do you have an Excel spreadsheet with cells that contain a mix of text and numbers? Do you need to extract just the numeric values from those cells? Fortunately, Excel provides several easy formulas to pull numbers from alphanumeric strings in cells. In this article, we’ll show you step-by-step how to use formulas like VALUE, NUMBERVALUE, and other functions to quickly get only the numbers from an Excel cell, leaving out any other characters.

Whether you’re working with product codes, serial numbers, phone numbers, or other data that combines numbers and text, these techniques will help you extract the numeric portions quickly and easily. You can then use the extracted numbers in calculations, charts, pivot tables, and other Excel features without the non-numeric characters getting in the way. To pull the web address out of a hyperlink, see how to extract a URL from a hyperlink.

Why VALUE Alone Doesn’t Work

VALUE converts text that is already a number, such as “123”, “$456.78” or “50%” (which becomes 0.5), into a real number. It does not strip out letters: =VALUE("123abc") and =VALUE("abc123") both return #VALUE!. NUMBERVALUE behaves the same way; it only adds control over decimal and group separators. Use the formulas below instead.

Method 1: Microsoft 365 Formulas

All digits in the cell, wherever they are (“AB-12-C3” returns 123):

=--TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1)*1, ""))

The first number, including decimals (“Price: $49.99 each” returns 49.99), with the REGEXEXTRACT function in current Microsoft 365:

=--REGEXEXTRACT(A2, "[0-9]+(\.[0-9]+)?")

Method 2: Formulas for Any Version of Excel

These classic formulas try every possible length and keep the longest piece that is a valid number. They don’t need Ctrl + Shift + Enter.

How they work: LEFT, RIGHT or MID return the first 1, 2, 3… up to 15 characters; -- turns each into a number or an error; and LOOKUP with a huge number (99^99) returns the last one that is a number, which is the complete number. In the “anywhere” version, SEARCH finds the position of the first digit. Change 15 if your numbers can be longer.

Method 3: Flash Fill (No Formula)

Next to the data, type the number from the first cell exactly as you want it, move to the cell below, and press Ctrl + E. Excel copies the pattern to the rest of the column. Check a few results, because Flash Fill guesses the pattern.

Get Numbers with Excel’s Text to Columns Feature

As an alternative to using formulas, Excel’s Text to Columns feature can also separate numbers mixed with text. This is a good choice if you just need to quickly extract numbers as a one-time task.

To use Text to Columns:

1. Select the cells you want to extract numbers from
2. Go to the Data tab and click Text to Columns
3. Choose Delimited and click Next
4. Check the box for Treat consecutive delimiters as one
5. Click each relevant delimiter like Space or Comma
6. Click Finish to split the values into columns

After separating the content into columns, you can select the columns with numbers and copy and paste the data wherever you need it.

Text to Columns is a quick and easy way to split combined data into separate cells. However, unlike formulas, it doesn’t automatically update if your source data changes. Only use this for one-off number extractions.

Summary

In summary, to extract and get a number from an Excel cell that contains other characters:

• In Microsoft 365, use TEXTJOIN with SEQUENCE to keep all digits, or REGEXEXTRACT for the first number with decimals
• In any version, use LOOKUP(99^99, --LEFT(...)), --RIGHT(...) or the MID + SEARCH version for numbers at the start, end or anywhere
• Use VALUE only when the cell already contains just a number stored as text
• Use Flash Fill (Ctrl + E) for a quick one-off without formulas
• Use Text to Columns to quickly split numbers into separate columns as an alternative to formulas

With these techniques in your toolkit, you can handle all sorts of mixed text-and-number data in Excel. Whether you’re dealing with serial numbers, product codes, phone numbers, or other alphanumeric combinations, you can extract the numeric components quickly and use them in your Excel formulas and features.

FAQs