search string for number value

S

samuel

I have several cells

text text 232
text more text 80908

I want a formula to find the location of the first occurance of a number.

something like find(#,a1)
 
T

T. Valko

Assuming every cell will contain a number:

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))
 
B

Bob Umlas, Excel MVP

Ctrl/Shift/Enter this:
=MATCH(FALSE,ISERROR(1*MID(A1,ROW(1:100),1)),0)
In your example this returns 11.

HTH
Bob Umlas
Excel MVP
 
R

Ron Rosenfeld

I have several cells

text text 232
text more text 80908

I want a formula to find the location of the first occurance of a number.

something like find(#,a1)
Click to expand...


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

And, if you want to return that number:

=LOOKUP(9.9E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))
--ron
 
Top