Extract Numbers from Alphanumeric Data

The following is the generic formula to extract the numbers from Alphanumeric Data.

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

As you can see in the following example, the formula returns all numerals from the text string supplied to it.

Even if this formula is handy in several situations, the tool which I prefer to Extract numbers from Alphanumeric data is Power Query.

Read my article, Remove or Extract Special Characters from a data set using Power Query

Following is a video which explains the use of Power Query to extract English Alphabets and Numbers from Alphanumeric data.

Excel Formulas

Excel Functions