Stripping Down Text

[Guest]

I am using excel 2000
I have a massive list of names and I want to extract the surname which is
the last item in the text string.
The "Search" function will operate from the left of the string but I cannot
find any function to operate from the right.
Using text to columns can take up to ten columns. Examples "Mr S. Bishop" ,
"Mr & Mrs S. Bishop" , "Mr S. Bishop & Mrs S. Bishop" etc.

Thank you in advance for your help

 

[Frank Kabel]

Hi
for getting the last word use one of nthe following formulas:
=MID(A1,FIND("^^",SUBSTITUTE(" "&A1," ","^^",LEN("
"&A1)-LEN(SUBSTITUTE(" "&A1," ",""))))-1+1,1024)
or
=MID(A1,LOOKUP(2,1/(MID(" "&A1,seq,1)=" "),seq)-1+1,1024)

for the second one: 'seq' is a defined name with the formula
=ROW(INDIRECT("1:1024"))

 

[Bob Phillips]

Suggested small change to Frank's first formula

=MID(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,1024)

 

[Guest]

Gentlemen,
Thank you for your help.
The two functions Frank sent did not work returning a "VALUE" and "NAME"
error respectively.

Bob's amendment worked - tkank you

 

[Guest]

I'm using Excel 2003 and trying to do the same thing -- extract the last name
from a cell containing the full name, e.g. "John and Jane Doe". Bob's
formula is leaving me with a #VALUE error. Is there another formula that
will work in the newer version of Excel?

 

[Dave Peterson]

Bob's formula got hit by line wrap. Maybe you had trouble because of that. (It
worked for me and should work ok in xl2003.)

=MID(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(""&A1)
-LEN(SUBSTITUTE(A1," ",""))))+1,1024)

(all one cell)

(I got the #value! error if I used this:
=MID(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(""&A1)
-LEN(SUBSTITUTE(A1,"",""))))+1,1024)
(note the missing space character in the second line)