# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Extract number from alphanumeric string

## laichiwai

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

----------


## shg

From your example, find and replace "test" with nothing.

----------


## laichiwai

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

----------


## NBVC

Try, In K1:




```
Please Login or Register  to view this content.
```


copied down

----------


## laichiwai

Yes...Thanks alot.
Really appreciate it

----------


## oldchippy

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

----------


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

----------


## oldchippy

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

----------


## NBVC

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.

----------


## daddylonglegs

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



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 [30] 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)))))

----------


## jasonames223

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

----------


## shg

Welcome to the forum, Jason.

Please tale a few minutes to read the forum rules, and then start your own thread.

Thanks.

----------

