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

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).
 
B

Bearacade

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
 
G

Guest

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
 
G

Guest

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

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.
 
R

Ron Rosenfeld

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).

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top