locating and extracting numeric characters in a text string

[bmantych]

Hi,

I am trying to figure out how to extract numbers that are in various positions within a text string.

For example, I have 'abc123ef' in a cell and I want a formula that will find where the number starts (in this case, position 4 but it won't be in the same place in each example) and ends and the pull just the number (123) out.

The next cell in the column may contain '12abcdefgh' and I'd need only to extract the '12'.

 

[Domenic]

Assuming that there are no other numbers within the text string, other than the ones appearing consecutively, try...

=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),SUMPRODUCT(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},""))))

Hope this helps!

 

[pgc01]

Beautiful formula, Domenic!
I was trying, but could't get the third parameter of the MID.

Cheers
PGC

 

[bmantych]

Excellent! Thanks for your help!

 

[Krishnakumar]

Another approach..

=LOOKUP(9.9999999999E+307,--MID(REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1,""),1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))))

HTH

 

[Domenic]

Another approach..

=LOOKUP(9.9999999999E+307,--MID(REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1,""),1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))))

HTH

Note, however, that the formula will omit any leading zeros...

 

[Krishnakumar]

Yes, I didn't notice that