Extract Number From Alphanumeric Text

**Excel Fox** · 10-16-2011, 11:15 PM

**Excel Fox** · 10-17-2011, 06:20 AM

A link that shows hows to extract numbers that are placed together. Ex. ABC123DEF

http://www.familycomputerclub.com/ex...eric-text.html

**Rick Rothstein** · 02-25-2012, 07:51 AM

Originally Posted by Excel Fox

A link that shows hows to extract numbers that are placed together. Ex. ABC123DEF

Extracting numbers from alphanumeric text

Here is another formula that does this...

=LOOKUP(9.9E+307,1*LEFT(MID(A1,MIN(FIND({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789")),999),ROW($1:$999)))

I think my formula has more characters in it, but it should still be faster than the code at that link because it has three less function calls and it is not array-entered (just press the ENTER key to commit it).

**Excel Fox** · 10-17-2011, 06:20 AM

And to illustrate how this code works

Consider the case fdsff43t59r178dq, where the output expected is 4359178

1. ROW(INDIRECT("1:"&LEN(A1))) is equivalent to ROW(INDIRECT("1:"&16)) which is equivalent to ROW(INDIRECT("1:16"))

When the above is used in an array formula, which also includes a SUMPRODUCT function, it acts like an array of values from 1 to 16, ie

{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}

2. MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) is equivalent to MID("fdsff43t59r178dq",{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16},1) which results in the array {"f", "d", "s", "f", "f", "4", "3", "t", "5", "9", "r", "1", "7", "8", "d", "q"}

3. From the above, ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1) would equate to ISNUMBER({"f", "d", "s", "f", "f", "4", "3", "t", "5", "9", "r", "1", "7", "8", "d", "q"}/1).

If you use any mathematical operation on a text that is numeric in nature, it will return another numeric value, and the other would return an error. So the above formula would essentially give an array {FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, FALSE}

4. Now comes LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))

For this you need to understand how the LARGE function works (I'll skip that).

The above equation would equate to

LARGE({FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, FALSE}
*{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16},{1,2,3,4 ,5,6,7,8,9,10,11,12,13,14,15,16}) which would then result as

LARGE({0, 0, 0, 0, 0, 6, 7, 0, 9, 10, 0, 12, 13, 14, 0, 0},{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}) which would then result as

{14,13,12,10,9,7,6,0,0,0,0,0,0,0,0,0}

5. Now comes the MID formula

MID(0&A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)

You all know how the MID formula works. MID(TextToLookAt,CharacterPositionToStartFrom,Leng thOfCharacters)

The character position to start from should not be less than 1. Our array above, ie, {14,13,12,10,9,7,6,0,0,0,0,0,0,0,0,0} has quite a few zeroes in it. To nullify a probable error in the formula, we add a 0 (zero) to the beginning of the original text, and we offset the array above by 1 (one). There's another reason why we add a zero to the beginning, which will be clear as we continue with the explanation.

So the MID formula would equate to

MID("0fdsff43t59r178dq", {15,14,13,11,10,8,7,1,1,1,1,1,1,1,1,1}, 1) would equate to {"8", "7", "1", "9", "5", "3", "4", "0", "0", "0", "0", "0", "0", "0", "0", "0"}

6. Now comes the really interesting part of the equation.

You know what x^y, ie, X powered by Y, means, so this should be straight forwards

MID(0&A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1) would equate to

{"8", "7", "1", "9", "5", "3", "4", "0", "0", "0", "0", "0", "0", "0", "0", "0"}^{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}-1 which equates to

{"8", "7", "1", "9", "5", "3", "4", "0", "0", "0", "0", "0", "0", "0", "0", "0"}^{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15} which equates to

{8, 70, 100, 9000, 50000, 300000, 4000000, 0, 0, 0, 0, 0, 0, 0, 0, 0}

7. SUMPRODUCT({8, 70, 100, 9000, 50000, 300000, 4000000, 0, 0, 0, 0, 0, 0, 0, 0, 0}) would give us the result 4359178

**rajeshd@live.in** · 01-16-2012, 04:56 PM

Can you please explain me use of having '0 & A' and also '^' in this below formula.

**Excel Fox** · 01-22-2012, 05:46 PM

0 is added just to ensure that there are no errors. Everything else can be found out by evaluating the formula. Post back if that's still not clear.

**Rajan_Verma** · 03-06-2012, 10:13 PM

Hi Rich

=LOOKUP(9.9E+307,1*LEFT(MID(A1,MIN(FIND({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789")),999),ROW($1:$999)))

this formula is not extracting all digits from alphanumeric value

My Input was a0-112455jkjk223
and result is coming 0

**Rick Rothstein** · 03-06-2012, 10:30 PM

Originally Posted by Rajan_Verma

=LOOKUP(9.9E+307,1*LEFT(MID(A1,MIN(FIND({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789")),999),ROW($1:$999)))

this formula is not extracting all digits from alphanumeric value

My Input was a0-112455jkjk223
and result is coming 0

My formula was not meant as a replacement for this article's original formula. If you look at the section I quoted, you will see I was responding to this statement from the second message in this thread...

"A link that shows how to extract numbers that are placed together. Ex. ABC123DE
http://www.familycomputerclub.com/ex...eric-text.html"

If the digits for the number are all placed together, my formula returns them... if they are not all placed together, then my formula returns the first complete number it comes to. For example, if the text were ab123cd45efg678hijk, then my formula would return 123... the first complete number in the string. For the example you posted, the 0 is the first complete number (it is followed by a dash, a non-digit), so it returns that value.

**Howardc** · 08-22-2012, 09:45 PM

Hi Guys

Thanks for all the input, much appreciated

**Excel Fox** · 09-11-2013, 08:36 PM

By the way, for a more lazy but shorter code to the original post, one can use this for let's say, text that don't have more than 25 characters

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

Picked it from the link posted here

How to separate numbers and words | Chandoo.org Excel Forums - Become Awesome in Excel