How do I search a text string in Excel from right to left?

[conoisseur]

I have a mailing list that has, in the "Name" field, two different names
(e.g. "John Smith and Mary Doe"). I want to pull out the two last names by
searching for the first space between words and the last space between words.
The first is easy. How do I find the last space in a text string?

 

[Ron Rosenfeld]

How do I find the last space in a text string?

With the text string in A1:

=FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

will give the character number of the last <space> in the string.


--ron

 

[Jason Morin]

First last name:

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND
(" ",A1))

Second last name:

=MID(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN
(SUBSTITUTE(A1," ",""))))+1,999)

HTH
Jason
Atlanta, GA