1st numer in string

J

Jack Deuce

I need to locate (=find maybe) the first occurance of a number but
starting from the last position of text because the first couple of
characters in the string may be numbers. Looks something like this
and is not fixed columns.

xxxxxxxx text here xxxxxxx
1/3 this is the text portion 11 22 33 44 55 66

Need to find the text position of the first 1 in 11.


Thanks
 
R

Ron Rosenfeld

I need to locate (=find maybe) the first occurance of a number but
starting from the last position of text because the first couple of
characters in the string may be numbers. Looks something like this
and is not fixed columns.

xxxxxxxx text here xxxxxxx
1/3 this is the text portion 11 22 33 44 55 66

Need to find the text position of the first 1 in 11.


Thanks

Slight modification to handle the case where there are NO alphabetical letters in the string:

=FIND(B1,A1,LOOKUP(2,1/((MID("z"&A1,ROW(
INDIRECT("1:"&LEN("z"&A1))),1)<>" ")*
ISERR(-MID("z"&A1,ROW(INDIRECT("1:"&LEN("z"&A1))),1))),
ROW(INDIRECT("1:1000"))))
 
H

Harlan Grove

Ron Rosenfeld said:
....
=FIND(B1,A1,LOOKUP(2,1/((MID("z"&A1,
ROW(INDIRECT("1:"&LEN("z"&A1))),1)<>" ")
*ISERR(-MID("z"&A1,ROW(INDIRECT("1:"&LEN("z"&A1))),1))),
ROW(INDIRECT("1:1000"))))

The original text appears to start with numerals, but they shouldn't
be included in the search. Assuming what's wanted is the position of
the first decimal numeral after the first space, then in Excel 2007 or
later, try the array formula

=MIN(IFERROR(FIND({0;1;2;3;4;5;6;7;8;9},A1,FIND(" ",A1)),FALSE))

and in Excel 2003 and prior, the array formula

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

Both return 0 when there are no spaces and/or no decimal numerals in
A1.
 
R

Ron Rosenfeld

The original text appears to start with numerals, but they shouldn't
be included in the search. Assuming what's wanted is the position of
the first decimal numeral after the first space, then in Excel 2007 or
later, try the array formula

=MIN(IFERROR(FIND({0;1;2;3;4;5;6;7;8;9},A1,FIND(" ",A1)),FALSE))

and in Excel 2003 and prior, the array formula

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

Both return 0 when there are no spaces and/or no decimal numerals in
A1.

I have not seen you posting for quite a while Harlan. It is good to see your contributions again. They are always thought provoking.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top