To pull only the digits out of a mixed value like “TPS1984“, use =TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1,"")). It splits the cell into single characters, multiplies each by 1 so letters become errors, IFERROR turns those into blanks, and TEXTJOIN glues the survivors back together.
On Excel 2019 it needs Ctrl+Shift+Enter; on 365 and 2021 a plain Enter works. The result is text, so add *1 at the end or wrap it in VALUE if you need a real number.
In Excel 365 there’s a one-line version that replaces all of this: =REGEXREPLACE(A1,"[^0-9]","") removes every non-digit, and =REGEXREPLACE(A1,"[^0-9]","")+0 returns it as a number. For a one-off cleanup with no formula, type the digits from the first row into the next column and press Ctrl+E for Flash Fill — it handles consistent patterns well.
And on Excel 2016 or earlier, where TEXTJOIN doesn’t exist, the SUMPRODUCT version is the fallback.
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:
- First, we have the LEN function to get the count of characters in the value.
- After that, INDIRECT uses a cell reference using the 1 and 7 (returned by the LEN).
- Next, the ROW function will use the INDIRECT and returns an array of number starting from 1 and ending with 7.
- Now, MID will use this array and return a new one with the values from the cell you referred to.
- 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.
- From here, IFERROR function will convert the error values into blank values.
- 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.
Get the Excel File
Related Formulas
- Create a Horizontal Filter in Excel
- Create a Star Rating Template in Excel
- Get File Name in Excel
- Get Sheet Name in Excel
- Quickly Generate Random Letters in Excel
- Randomize a List (Random Sort) in Excel
- Count Characters in Excel (Cell and Range)
- Get File Path (Excel Formula)
- Get the Value from a Cell
-
Back to the List of Excel Formulas
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.