Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
1st numer in string
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
1st numer in string
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")))) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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")))) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
1st numer in string
On Tue, 06 Dec 2011 12:39:10 -0800, Gord Dibben wrote:
Ron How do you come up with these<g Gord Thanks, Gord. But there is an error in the algorithm that makes it not work if the string does not contain a letter. <sigh Back to the drawing board. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
1st numer in string
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 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")))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
same numer/character at beginning of cells in row | Excel Discussion (Misc queries) | |||
Formula first time numer appears | Excel Worksheet Functions | |||
Find first numer in a Column | Excel Worksheet Functions | |||
Reomving a set numer of characters from a text string | Excel Discussion (Misc queries) | |||
Can I change line numer or column name | Excel Discussion (Misc queries) |