Thread: Extract number from alphanumeric string

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

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

Yes...Thanks alot.
Really appreciate it

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,SU BSTITUTE(J1&0,{1,2,3,4,5,6,7,8,9},0)))-1,"")+0


Thanks & Best Regards,
ChiWai

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,SU BSTITUTE(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 [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)))))

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