find a number in a cell

Yeah, that'll work. I didn't have time to dig any deeper and look for a
tweak.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
So, as long as the next character after the last number that
is 12 "a" it will work!

Well, not exactly... if the character is a "p", you will have the same
problem (for the same reason). Here is a patch to your formula to fix this
problem...

=LOOKUP(1000,--MID(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"a","z"),"p"," z"),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3,4,5 ,6,7,8,9},SEARCH("
at ",A1))),ROW(INDIRECT("1:255"))))

Yes, your formula is now longer, but it still is around half the size of
the one I posted.

Rick

I'm not 100% sure why this works, but it does seem to work. This formula
removes one of the SUBSTITUTE function calls and arrays the "find me" text
(saving some 15 characters in the process)... and it seems to always return
the correct answer too:

=LOOKUP(1000,--MID(SUBSTITUTE(LOWER(A1),{"a","p"},"z"),MIN(FIND({ 0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3,4,5,6,7,8,9},SEAR CH("
at ",A1))),ROW(INDIRECT("1:255"))))

Rick


"T. Valko" wrote in message
...
Yeah, that'll work. I didn't have time to dig any deeper and look for a
tweak.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
So, as long as the next character after the last number that
is 12 "a" it will work!

Well, not exactly... if the character is a "p", you will have the same
problem (for the same reason). Here is a patch to your formula to fix
this problem...

=LOOKUP(1000,--MID(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"a","z"),"p"," z"),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3,4,5 ,6,7,8,9},SEARCH("
at ",A1))),ROW(INDIRECT("1:255"))))

Yes, your formula is now longer, but it still is around half the size of
the one I posted.

Rick