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.

Using the VALUE Function to Extract Numbers from Cells

One of the simplest ways to extract a number from a cell in Excel is by using the VALUE function. The VALUE function converts a text string that represents a number to an actual numeric value.

Here’s how to use the VALUE function:

1. Select the cell where you want the extracted number to appear.
2. Type =VALUE(
3. Click on the cell that contains the alphanumeric value you want to extract the number from.
4. Type a closing parenthesis ) and press Enter.

The VALUE function will return just the numeric portion of the referenced cell. Note that this only works if the string begins with the number. For example:

As you can see, the VALUE function successfully extracts the leading number from cells A2, A3, and A4. However, it returns a #VALUE! error for cell A5 because the number is not at the start of the string.

The VALUE function is a good choice when you know the numeric portion of the string always comes first. It’s a simple and efficient formula for this case.

Extract Numbers Using the NUMBERVALUE Function

Excel’s NUMBERVALUE function is similar to VALUE but provides additional options for extracting numbers from strings. It can handle numbers that use a different decimal separator than your locale uses.

To use NUMBERVALUE to get a number from a cell:

1. Select the cell where you want the result.
2. Enter =NUMBERVALUE(
3. Select the cell containing the value to extract the number from.
4. Optionally type a comma and the decimal and group separators in quotes (e.g. =NUMBERVALUE(A2,".")).
5. Type a closing parenthesis and hit Enter.

Some examples of using NUMBERVALUE:

Like VALUE, NUMBERVALUE only works if the number is at the beginning of the string. The additional arguments let you specify the group and decimal separators if they differ from your local settings.

NUMBERVALUE is useful when the numbers in your data use formatting that is different from your location’s defaults. By specifying the separators, you ensure the numbers are interpreted correctly.

Combining LEFT, LEN, and ISNUMBER to Extract Numbers

What if you need to extract a number that isn’t at the start of the string in a cell? By nesting the LEFT, LEN, and ISNUMBER functions, you can test different lengths of the left portion of a string until you extract just the leading number.

Here are the steps:

1. Select the result cell and type =
2. Enter LEFT( and select the cell with the alphanumeric string
3. Type a comma, then LEN( and again select the alphanumeric cell
4. Enter )-LEN(ISNUMBER(LEFT( and once more select the cell
5. Finish with two closing parentheses and press Enter

In more compact form, the formula looks like this:

=LEFT(A2,LEN(A2)-LEN(ISNUMBER(LEFT(A2))))

To break this down:

• The outer LEFT function returns characters from the left of the string in A2
• The LEN functions calculate how many characters to extract
• The ISNUMBER(LEFT(...)) portion tests increasing lengths of the left substring until it is no longer a valid number

This will extract numbers from entries like:

• A2: Price: $49.99 each
• A3: 25pcs available
• A4: The answer is 42
• A5: 867-5309 Jenny

While more complex than the VALUE function, this formula is very flexible. It will find and extract a number even if it’s not at the very beginning of the string.

Extract Number from Right of String Using VALUE and RIGHT

You can also get numbers from the right side of a string by combining the VALUE and RIGHT functions:

1. In the destination cell, enter =VALUE(RIGHT(
2. Click the cell you want to extract a number from and type a comma
3. Enter LEN( and again click the cell with the alphanumeric value
4. Type )-LEN(ISNUMBER(RIGHT(
5. Select the cell a third time, type )))), and hit Enter

The full formula is:

=VALUE(RIGHT(A2,LEN(A2)-LEN(ISNUMBER(RIGHT(A2)))))

This works similarly to the LEFT version, except it takes characters from the right of the string and VALUE converts the result to a number. It will extract trailing numbers from entries like:

• A2: Product XJ-42
• A3: Call 555-1234 for info
• A4: Our goal is 90% satisfaction
• A5: Serial no. 8675309

Use this option when you need to pull out numeric data that comes at the end of text strings. It saves you from having to manually separate the number from the preceding characters.

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:

• Use the VALUE function for a number at the start of a cell
• Use NUMBERVALUE to extract a number and handle different decimal/group separators
• Combine LEFT, LEN, ISNUMBER to test for a number from the left of a string
• Nest VALUE, RIGHT, LEN, ISNUMBER to extract a number at the right of a string
• 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

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.