Extract numeric portion from an alphanumeric string in Excel 2010

Question

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

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.

I would Regex functions through a User Defined Function. See this post: https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — Frank Ball, Apr 16 '19 at 04:17
@FrankBall I was just looking at the last line that said preferred to do it without a macro. — Forward Ed, Apr 16 '19 at 04:32

Forward Ed · Answer 1 · 2019-04-16T10:53:03.500

Going basic and old school and long winded:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A2),"a",""),"b",""),"c",""),"d",""),"e",""),"f",""),"g",""),"h",""),"i",""),"j",""),"k",""),"l",""),"m",""),"n",""),"o",""),"p",""),"q",""),"r",""),"s",""),"t",""),"u",""),"v",""),"w",""),"x",""),"y",""),"z","")

Basically it goes through and looks for each character of the alphabet and replaces it with "" It will remain as a string in order to show leading zero. if you want it as a number, leading zero will not show and you need to send the string through a math operation that will not change its value such as:

--
+0
-0
*1
/1

The LOWER function converts the characters all to lower case. It saves doing a substitute twice. Once for lower case and once for upper case.

CAVEAT: IF there are special characters such as "!@#$%^&*()_+-=[]{}|:";'<>?, ./" the current formula will leave them untouched. A SUBSTITUTE for each special character would need to be removed. Same goes for characters like "éìô" etc

Looking at how Excel would handle (text that looks like) numbers I think your solution is the only option I can see if OP wants to avoid VBA. The last bit assumes numbers, but will get messed up by Excel when more than 15 digits. — JvdV, Apr 16 '19 at 07:58
Note for OP: If these are always going to be Hex, like the example given, then you only need to replace "a", "b", "c", "d", "e", "f", and not the rest of the alphabet — Chronocidal, Apr 16 '19 at 10:57

Gary's Student · Answer 2 · 2019-04-16T14:09:18.337

1

With data in A1, in B1 enter the array formula:

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

or even this array formula (available in Excel 365):

=TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),""))

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

edited Apr 16 '19 at 14:09

answered Apr 16 '19 at 04:33

Gary's Student

95,722
10
59
99

1

Nice one, only thing is that `TEXTJOIN()` function won't be available in Excel 2010, also the scientific notation of Excel on numbers will be a problem. – JvdV Apr 16 '19 at 07:01
FYI TEXTJOIN not available in Excel 2013. – Forward Ed Apr 16 '19 at 10:58

Chronocidal · Answer 3 · 2019-04-16T11:11:21.090

Here is a method using an Array Formula:

=CONCAT(IFERROR(MID(A2,ROW(OFFSET($A$1,0,0,LEN(A2),1)),1)/1,""))

Literally splits the text into an array of single-characters, then checks if each is a number (i.e. can it be divided by 1).

If not, the character is with an empty string (""), and it then squishes everything back together again at the end. Remember to use Ctrl+Shift+Enter when you put it in.

(CONCAT, like TEXTJOIN is not available in Excel 2010 or earlier.)

score 1 · Accepted Answer · answered Apr 16 '19 at 17:42

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))

Michael · Answer 5 · 2019-04-16T04:54:56.663

0

It looks like you've only got letters a to f to remove. If that's the case, this might be quicker than a solution that handles all possible letters:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"a",""),"b",""),"c",""),"d",""),"e",""),"f","")

edited Apr 16 '19 at 04:54

answered Apr 16 '19 at 04:43

Michael

4,563
2
11
25

Extract numeric portion from an alphanumeric string in Excel 2010

5 Answers5