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

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

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)

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

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