Extract Only Numbers from a Text (String)

In Excel, you can use formulas to extract the number from a value with text and numbers combined. In the following example, we have used a formula to get 1984 from the value “TPS1984”.

Formula to Extract Numbers from a Text

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

In this tutorial, we will understand this formula in detail. And to understand it, we need to split it into parts:

1. First, we have the LEN function to get the count of characters in the value.
   
2. After that, INDIRECT uses a cell reference using the 1 and 7 (returned by the LEN).
   
3. Next, the ROW function will use the INDIRECT and returns an array of number starting from 1 and ending with 7.
   
4. Now, MID will use this array and return a new one with the values from the cell you referred to.
   
5. Next, we have a simple multiplication formula to multiply the array with 1. With this simple multiply, you get a new array where you will have #VALUE! Error for text values and numbers will stay intact.
   
6. From here, IFERROR function will convert the error values into blank values.
   
7. And in the end, TEXTJOIN will combine the values, and you’ll have only numbers in the result.
   

The above formula only works for the Excel version 2019 and above. It is because we are using TEXTJOIN, unavailable in the earlier versions.

If you want value for a part of the formula, select that part and press the F9 key from the shortcut.

TEXTJOIN is a volatile function that changes value when you update any cell in the worksheet. It can make your workbook a little slow.

But What IF I’m using an Earlier Version (2007, 2010,2013, 2016)

If you use the earlier versions, you must use a different formula. A formula like the following:

=IF(SUM(LEN(A1)-LEN(SUBSTITUTE(A1, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1))* ROW(INDIRECT("$1:$"&LEN(A1))),0), ROW(INDIRECT("$1:$"&LEN(A1))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A1)))/10),"")

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)

Both above formulas work fine with the different kinds of values. I have tested it in the following way.

In cell A1, above we have a complex value consisting of text, numbers, and symbols and we have only numbers in the result.