How can I extract numbers from a string using Excel?

By /

Extracting numbers from a string using Excel refers to the process of isolating and retrieving numerical values from a string of characters or text in an Excel spreadsheet. This can be achieved by using various functions such as the “LEFT”, “RIGHT”, “MID”, or “FIND” function to extract specific digits or by converting the entire string into a numerical value using the “VALUE” function. By doing so, users can easily manipulate and perform calculations on the extracted numbers, making data analysis and organization more efficient and accurate.

Excel: Extract Numbers from String

You can use the following formula to extract numbers from a string in Excel:

=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))

This particular formula will extract all numbers from the string in cell A2.

For example, if cell A2 contains the phrase “25 bikes” then this formula will simply return 25.

The following example shows how to use this formula in practice.

Example: Extract Numbers from String in Excel

Suppose we have the following list of strings in Excel:

Suppose we would like to extract only the numbers from each string.

We can type the following formula into cell B2 to do so:

=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))

We can then click and drag the formula down to each remaining cell in column B:

Excel extract number from string

Column B now contains only the numbers from each of the corresponding strings in column A.

How This Formula Works

Recall the formula that we used to extract the numbers from the strings:

=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))

First, ROW(INDIRECT(“1:”&LEN(A2))) returns a series of numbers from 1 to the length of the string.

Next, (MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1) converts all text characters to #VALUE! errors.

Next, IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””) removes all $VALUE! errors.

Lastly, TEXTJOIN(“”,TRUE,IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),””)) joins together all the numbers that remain and ignores any blanks.

The end result is that we’re able to extract only the numbers from each string.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

x

Related terms:

  1. Excel: Extract Date from Text String How do i extract a date from a text string?
  2. How to Extract Numbers from String in SAS
  3. How to extract numbers from string in Google Sheets?
  4. How do I find the first 3 positive numbers in Excel when I have a column of numbers?
  5. How can I extract the first number from a string using Excel?
  6. How do I extract the first character from a string in Excel?
  7. How can I extract a date from a text string in Excel?
  8. How can I extract an email address from a text string in Excel?
  9. How can I extract a decimal number from a string using Excel?
  10. Excel: Extract First Number from String