View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben[_2_] Gord Dibben[_2_] is offline
external usenet poster
 
Posts: 621
Default 1st numer in string

Ron

How do you come up with these<g


Gord

On Tue, 06 Dec 2011 15:20:24 -0500, Ron Rosenfeld
wrote:

On Tue, 06 Dec 2011 07:01:59 -0600, Jack Deuce wrote:

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


I'm not sure exactly what you want, but you should be able to work with this:

The last non-number, non-space character is given by the formula:

=LOOKUP(2,1/((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<" ")*ISERR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),ROW(INDIRE CT("1:1000")))

The "1000" at the end just needs to be any number longer than the length of the string.

So to find the position of the first "1", with your text string in A1, you can use:



=FIND(1,A1,LOOKUP(2,1/((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<" ")*ISERR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),ROW(INDIRE CT("1:1000"))))