How to find the LAST instance of a character in a text string

[Guido]

It's simple to find the first position of a certain character in a certain
text string, using FIND or SEARCH.
However, I would like to find the LAST instance of a certain character
within a certain text string.

Example:
Value of Cell A1: Guido Louc Johannes Makor
I wish to find the position of the last space within the text in Cell A1,
which should be 20. Thus I can extract my surname from A1 (using sth like
RIGHT(A1,LEN(A1)-'position of last space char'+1)).

Any help is appreciated!

Regards,
Guido

--
PS: To reply to this mail, PLEASE REMOVE '.SSPTAOMP'. This is a SPAM
blocker.

[Leo Heuser]

Guido

This formula will extract the last word in cell A1:

=RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1,"
","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

The symbol @ must not reside in the cell in question.
Please notice, that a space exists in two positions in the formula
at A1, " " (space between the double quotes).
The e-mail client may word wrap just there, so beware :-)

--
Best regards
LeoH
Excel MVP

"Guido" <gmakor....@usa.net> skrev i en meddelelse
news:Ub2k7.5632$fU2.1...@news.quicknet.nl...

[Guido]

Hi Leo,

That's really clever; wish I'd come up with it myself ;o)

Thanks!

Regards,
Guido

"Leo Heuser" <leo.h...@get2net.dk> wrote in message
news:Opm5Z4sMBHA.2272@tkmsftngp02...

[Leo Heuser]

You're welcome!

"Guido" <gmakor....@usa.net> skrev i en meddelelse

news:nD4k7.6193$fU2.1...@news.quicknet.nl...

[Harlan Grove]

Leo Heuser <leo.h...@get2net.dk> wrote...

>This formula will extract the last word in cell A1:
>
>=RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1,"
>","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

...

>"Guido" <gmakor....@usa.net> skrev i en meddelelse

>>However, I would like to find the LAST instance of a certain character
>>within a certain text string.

For the heck of it, I thought I'd answer the exact problem posed - find the
last substring in a string and (implicitly) return its position within the
string.

=FIND(CHAR(1),SUBSTITUTE(A1,A2,CHAR(1),
(LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")))/LEN(A2)))

It's highly unlikely CHAR(1) would be in any user-entered text cells. This
isn't an array formula, and it works with arbitrary substrings (A2).

FWIW, Leo's formula only works with single character search strings. Here's
an alternative that works with arbitrary substrings.

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,A2,CHAR(1),
(LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")))/LEN(A2)))+LEN(A2),256)