How do I retrieve the text string from the right of a cell

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

Guest

I have a list of names that include first (sometimes middle) and last names.
I need to just pull out the last names. The length is not constant so I
cannot use the RIGHT function (as this only pulls the characters, not the
string). Sometimes there is a middle name or intial, so I can't just count
to the space (sometimes there are two spaces, or more).
 
Try this


=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,
","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

The ONLY thing this won't do is extract people with a two word las
name
 
Try this:

For text in A1

This formula returns the text after the last space in A1...
B1: =RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND("
",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1)))))

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"
","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))
Click to expand...

That's perfect. Thank you. Sure, it also doesn't get it when someone uses
the suffix "Jr" at the end, but it's better then I had.
 
I have a list of names that include first (sometimes middle) and last names.
I need to just pull out the last names. The length is not constant so I
cannot use the RIGHT function (as this only pulls the characters, not the
string). Sometimes there is a middle name or intial, so I can't just count
to the space (sometimes there are two spaces, or more).
Click to expand...

You can use regular expressions to strip off the unwanted suffixes, and then
extract the last name.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

You can then use this formula:

=REGEX.MID(REGEX.SUBSTITUTE(A1,"(Sr|Jr|I{2,3}|IV|MD|M\.D\.|PhD|\s+)$"),"\w+",-1)

Note the mid portion of the formula:

"(Sr|Jr|I{2,3}|IV|MD|M\.D\.|PhD)$"

The bar-separated list between the parentheses is the list of unwanted
suffixes. Periods have to be preceded by a slash, hence the M\.D\. for MD.

the I{2,3} phrase will handle II or III

Other suffixes can be added.

The $ outside the parentheses signifies the end of the string, so that these
suffixes will only be removed if they are at the end.

Any trailing comma left after removing the suffixes will be ignored.


--ron
 
Back
Top