Separate last word in cell with more than 2 words?

I know how to use =RIGHT to separate the last name when there are only 1st
and last name. How do I separate the last name when there are 3 or more
words in the name? EX: Linda Jane Ellen Doe or Mrs. Linda Ellen Doe I have
a column of names for an address list but the entire name was keyed into each
cell in column A (Mr. & Mrs. John H. Doe). I want to separate the last name
so that I can sort the list of names in alphabetical order.

Try this one

=IF(ISERR(FIND(CHAR(32),TRIM(A3),1)),"",MID(A3,FIND("^^",SUBSTITUTE(A3,CHAR(32),"^^",LEN(""&A3)-LEN(SUBSTITUTE(A3,CHAR(32),""))))+1,1024))

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Pat Mayton" <Pat [email protected]> wrote in message news:[email protected]...
>I know how to use =RIGHT to separate the last name when there are only 1st
> and last name. How do I separate the last name when there are 3 or more
> words in the name? EX: Linda Jane Ellen Doe or Mrs. Linda Ellen Doe I have
> a column of names for an address list but the entire name was keyed into each
> cell in column A (Mr. & Mrs. John H. Doe). I want to separate the last name
> so that I can sort the list of names in alphabetical order.

On Thu, 24 Mar 2005 07:13:04 -0800, "Pat Mayton" <Pat
[email protected]> wrote:

>I know how to use =RIGHT to separate the last name when there are only 1st
>and last name. How do I separate the last name when there are 3 or more
>words in the name? EX: Linda Jane Ellen Doe or Mrs. Linda Ellen Doe I have
>a column of names for an address list but the entire name was keyed into each
>cell in column A (Mr. & Mrs. John H. Doe). I want to separate the last name
>so that I can sort the list of names in alphabetical order.


=IF(COUNTIF(A1,"* *")=0,A1,MID(SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))),1+FIND(CHAR(1),SUBSTITUTE(
A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),255))


--ron