Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Location of the last "s" in a text string
I've got a cell with some text in it.
Example - A1 has the text "This is a test" I'm trying to come up with a formula that will tell me the location of the last "s" in this cell. I've tried using a FIND function, but that will only give me the first occurrence starting at whatever position I specify. How can I get the location of the last "s" without knowing a starting position? Thanks, Paul |
#2
|
|||
|
|||
Location of the last "s" in a text string
Hi!
Try this: =FIND("~",SUBSTITUTE(A1,"s","~",LEN(A1)-LEN(SUBSTITUTE(A1,"s","")))) Note: no error checking. If "s" doesn't exist = #VALUE The tilde chararter is used as a "marker". The "marker" can be any unique character(s) that DOES NOT appear in the string. Biff "PCLIVE" wrote in message ... I've got a cell with some text in it. Example - A1 has the text "This is a test" I'm trying to come up with a formula that will tell me the location of the last "s" in this cell. I've tried using a FIND function, but that will only give me the first occurrence starting at whatever position I specify. How can I get the location of the last "s" without knowing a starting position? Thanks, Paul |
#3
|
|||
|
|||
Location of the last "s" in a text string
PCLIVE wrote...
I've got a cell with some text in it. Example - A1 has the text "This is a test" I'm trying to come up with a formula that will tell me the location of the last "s" in this cell. I've tried using a FIND function, but that will only give me the first occurrence starting at whatever position I specify. How can I get the location of the last "s" without knowing a starting position? There are a few approaches. Define a name like seq referring to the formula =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,1024,1)) [using this rather than =ROW($1:$1024) makes the name's definition impervious to row insertion/deletion, and it avoids volatile functions]. Then use the nonarray formula =LOOKUP(2,1/(MID(A1,seq,1)="s"),seq) or the array formula =MAX((MID(A1,seq,1)="s")*seq) |
#4
|
|||
|
|||
Location of the last "s" in a text string
Thanks Biff.
I'm not sure what if anything is different, but I also got it to work with: =FIND("^^",SUBSTITUTE(" "&A1," ","^^",LEN(" "&A1)-LEN(SUBSTITUTE(" "&A1," ",""))))-1 "Biff" wrote in message ... Hi! Try this: =FIND("~",SUBSTITUTE(A1,"s","~",LEN(A1)-LEN(SUBSTITUTE(A1,"s","")))) Note: no error checking. If "s" doesn't exist = #VALUE The tilde chararter is used as a "marker". The "marker" can be any unique character(s) that DOES NOT appear in the string. Biff "PCLIVE" wrote in message ... I've got a cell with some text in it. Example - A1 has the text "This is a test" I'm trying to come up with a formula that will tell me the location of the last "s" in this cell. I've tried using a FIND function, but that will only give me the first occurrence starting at whatever position I specify. How can I get the location of the last "s" without knowing a starting position? Thanks, Paul |
#5
|
|||
|
|||
Location of the last "s" in a text string
Hi!
Yeah, that'll work. Actually, that's a pretty clever way to build an error trap. Biff "PCLIVE" wrote in message ... Thanks Biff. I'm not sure what if anything is different, but I also got it to work with: =FIND("^^",SUBSTITUTE(" "&A1," ","^^",LEN(" "&A1)-LEN(SUBSTITUTE(" "&A1," ",""))))-1 "Biff" wrote in message ... Hi! Try this: =FIND("~",SUBSTITUTE(A1,"s","~",LEN(A1)-LEN(SUBSTITUTE(A1,"s","")))) Note: no error checking. If "s" doesn't exist = #VALUE The tilde chararter is used as a "marker". The "marker" can be any unique character(s) that DOES NOT appear in the string. Biff "PCLIVE" wrote in message ... I've got a cell with some text in it. Example - A1 has the text "This is a test" I'm trying to come up with a formula that will tell me the location of the last "s" in this cell. I've tried using a FIND function, but that will only give me the first occurrence starting at whatever position I specify. How can I get the location of the last "s" without knowing a starting position? Thanks, Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove last character of text string | Excel Worksheet Functions | |||
How to import a text file to Excel treating all input content are in string. | Excel Discussion (Misc queries) | |||
EXTRACT TEXT FROM TEXT STRING | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Formating a text string? | Excel Discussion (Misc queries) |