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:
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),""))
Here is how this formula works:
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:
Excel: How to Extract Text Between Two Characters
Excel: How to Extract Text After a Character
Excel: How to Extract Text Before a Character
