Stripping Down Text

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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"))
 
Suggested small change to Frank's first formula

=MID(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(""&A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,1024)
 
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
 
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?
 
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)
 
Back
Top