Search code examples
excelexcel-formulanumericalphanumeric

Extract numeric portion from an alphanumeric string in Excel 2010

I would like to extract all the numbers from an alphanumeric string in excel. I have an excel sheet with list of alphanumeric strings as shown below and I would like to extract all the numbers from the alphanumeric string and store it in a new cell

enter image description here

I already tried the below formula found online but it outputs '6' as result but it isn't right, so can anyone please help me with it? 

SUM(MID(0&A2,LARGE(ISNUMBER(-- 
MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),
ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)

I would expect the output of this string:

eed1e11bd1a66cb47ad8b215c882194cdf964332484d20c56aea69e6e5196f67

to be:

1111664782158821949643324842056696519667

Please note that I wish to do this only via Excel. Preferrably some functions rather than macro.

Solution

  • Here's a UDF that will handle this using REGEX (normally the fastest way to handle complex string manipulations). It removes everything that isn't a number and returns it as a string. 

    Function NumbersOnly(rng As Range)
 Dim nReturn As Variant
 With CreateObject("VBScript.RegExp")
        .Pattern = "[^0-9]"
        .MultiLine = True
        .Global = True
        nReturn = .Replace(rng.Value2, vbNullString)
    End With
    NumbersOnly = nReturn
End Function

    If you want a number simply wrap the function in a "VALUE" function.

    =VALUE(NumbersOnly(A1))