# Extract number from alphanumeric string

1. ## Extract number from alphanumeric string

Hi all,

What is the formula to be used to extract number from a alphanumeric string located at different row?

example:
test123 test128 test131
test124 test129 test132
test125 test130 test133
test126 test131 test134
test127 test132 test135
test128 test133 test136

result:
123 128 131
124 129 132
125 130 133
126 131 134
127 132 135
128 133 136

Thanks & Best Regards,
Chiwai  Register To Reply

2. From your example, find and replace "test" with nothing.  Register To Reply

3. ## Extract number from alphanumeric string

Yes... thanks many.
But, what is the formula to extract the number without corrupt the original data, can the result return at some where else?

an example:
J1 = 251 chances
J2 = 1250chances
J3 = red 1000chances
J4 = Blue30chances
J5 = White222222

Result display:
K1 = 251
K2 = 1250
K3 = 1000
K4 = 30
K5 = 222222

Thanks & Best Regards,
Chiwai  Register To Reply

4. Try, In K1: ``Please Login or Register  to view this content.``
copied down  Register To Reply

5. ## Extract number from alphanumeric string

Yes...Thanks alot.
Really appreciate it  Register To Reply

6. An alternative, less typing

=LOOKUP(99^99,--("0"&MID(J1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},J1&"0123456789")),ROW(\$1:\$10000))))  Register To Reply

7. ## Extract number from alphanumeric string

Hi all,

How do i unscramble this 2 formula functions?

=LOOKUP(99^99,--("0"&MID(J1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},J1&"0 123456789")),ROW(\$1:\$10000))))

=REPLACE(LEFT(J1,LOOKUP(10,MID(J1,ROW(INDIRECT("1:30")),1)+0,ROW(INDIRECT("1:30")))),1,MIN(FIND(0,SUBSTITUTE(J1&0,{1,2,3,4,5,6,7,8,9},0)))-1,"")+0

Thanks & Best Regards,
ChiWai  Register To Reply

8. Hi Chiwai,

How do i unscramble this 2 formula functions?

=LOOKUP(99^99,--("0"&MID(J1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},J1&"0 123456789")),ROW(\$1:\$10000))))
I'm not an MVP so I can honestly say I don't know how it works, other than is does. I've obviously come across this formula, may here on this forum, I didn't make a note at the time.

But here's another function that does exactly the same with an explanation from Ashish Mathur, a Microsoft MVP (Most Valuable Professional)....hope this helps.

May be I should read it too!

http://office.microsoft.com/en-us/ex...549011033.aspx  Register To Reply

9. Also, if you select the cell with the formula and go to Tools|Formula Auditing|Evaluate Formula... you can step through the formula as it evaluates it to see what happens and when....this should help understand how the formula(s) work.  Register To Reply

10. Originally Posted by laichiwai
Hi all,

How do i unscramble this 2 formula functions?

=LOOKUP(99^99,--("0"&MID(J1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},J1&"0 123456789")),ROW(\$1:\$10000))))

=REPLACE(LEFT(J1,LOOKUP(10,MID(J1,ROW(INDIRECT("1:30")),1)+0,ROW(INDIRECT("1:30")))),1,MIN(FIND(0,SUBSTITUTE(J1&0,{1,2,3,4,5,6,7,8,9},0)))-1,"")+0

Thanks & Best Regards,
ChiWai
Here's how the first one works,

Firstly, normally formulas "start" somewhere in the middle when you want to decipher them!

This part

SEARCH({0,1,2,3,4,5,6,7,8,9},J1&"0123456789")

finds the position of the first of each digit in the string in J1, J1 is concatenated with 0123456789 so that the SEARCH function doesn't return an error [note: if you use 1234567890 with zero at the end you can get rid of the quotes]. So imagine J1 contains the string "Blue30chances"

Then this returns the array

{6,15,16,5,18,19,20,21,22,23}

6 is the position of the zero, 5 the position of the 3 and the other numbers are bigger than the string length because they represent the positions of 1,2,4 etc in the string "Blue30chances0123456789"

MIN now takes the smallest number in that array, i.e. 5, representing the position of the first digit in the string.

Now MID function comes into play as

MID(J1,5,ROW(\$1:\$10000))

[I've replaced the SEARCH function with its result, i.e. 5]

Using ROW(\$1:\$10000) is possibly overkill but it allows for the string to be up to 10000 characters long [note: this isn't particulary "robust", inserting rows in your spreadsheet could cause the formula to fail]

ROW(\$1:\$10000) generates an array of 10000 numbers, every number from 1 to 10000, effectively allowing the MID function to generate up to 10000 substrings, starting at the first digit, limited by the length of J1, so in our example MID returns an array a bit like this

{"3";"30";"30c";"30ch";"30cha";"30chan";"30chanc";"30chance";"30chances";"30chances";"30chances";...etc.}

MID function has "0" concatenated to the front of it, probably to avoid an error [and return 0] if J1 is blank

Now the -- converts the above array to values, text can't convert to values so you get errors like this

{3;30;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE...etc.}

Note that the value we want  is the last numeric value in the array.

When you use LOOKUP with a lookup value as an infeasibly large number, e.g. 99^99 you extract the last numeric value from the array, so formula returns 30.

Given my comments above I'd probably modify to

=LOOKUP(99^99,--(0&MID(J1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},J1&1234567890)),ROW(INDIRECT("1:"&LEN(J1)+1)))))  Register To Reply

11. ## Re: Extract number from alphanumeric string

Been struggling with this for a few hours, I'm a beginner

Want to extract isbn number from column A strings and dump them into column B. I'm getting the feeling this will take multiple formulas to get proper final output?

A1 = Introduction to Environmental Geology ISBN: 0-13-144764-5
A2 = ISBN: 0-534-62361-1 Good condition
A3 = ISBN: 080537146X

B1 = 0131447645
B2 = 0534623611
B3 = 080537146X

Thank you  Register To Reply

12. ## Re: Extract number from alphanumeric string

Welcome to the forum, Jason.

Thanks.  Register To Reply