Extract Number From Alphanumeric Text

To extract the numeric values from an alpha numeric text, use any of the following array formulas

{=SUM(MID(0&A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT( "1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&L EN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))}

OR

{=SUM(IFERROR(MID(A1,LARGE(ISNUMBER(MID(A1,ROW(IND IRECT("1:"&LEN(A1))),1)^0)*ROW(INDIRECT("1:"&LEN(A 1))),ROW(INDIRECT("1:"&LEN(A1)))),1),0)*(1&REPT("0 ",(ROW(INDIRECT("1:"&LEN(A1)))-1))))}

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=317218#p317218
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=317006#p317006
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

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

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

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:"&L EN(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:"&L EN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&L EN(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:"&L EN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&L EN(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

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

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.

Quote:

---

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

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

Quote:

---

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.

Hi Guys

Thanks for all the input, much appreciated

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

Hi Excelfox

Thanks for the advise, much appreciated

Howard


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=318868#p318868
https://eileenslounge.com/viewtopic.php?p=318311#p318311
https://eileenslounge.com/viewtopic.php?p=318302#p318302
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317704#p317704
https://eileenslounge.com/viewtopic.php?p=317857#p317857
https://eileenslounge.com/viewtopic.php?p=317541#p317541
https://eileenslounge.com/viewtopic.php?p=317520#p317520
https://eileenslounge.com/viewtopic.php?p=317510#p317510
https://eileenslounge.com/viewtopic.php?p=317547#p317547
https://eileenslounge.com/viewtopic.php?p=317573#p317573
https://eileenslounge.com/viewtopic.php?p=317574#p317574
https://eileenslounge.com/viewtopic.php?p=317582#p317582
https://eileenslounge.com/viewtopic.php?p=317583#p317583
https://eileenslounge.com/viewtopic.php?p=317605#p317605
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317030#p317030
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316412#p316412
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316046#p316046
https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
https://www.youtube.com/@alanelston2330
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
https://eileenslounge.com/viewtopic.php?p=316154#p316154
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://eileenslounge.com/viewtopic.php?p=317050#p317050
https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316057#p316057
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=316705#p316705
https://eileenslounge.com/viewtopic.php?p=316704#p316704
https://eileenslounge.com/viewtopic.php?p=176255#p176255
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA