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

[JWG]

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

[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 last name.

[Ron Coderre]

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


"JWG" wrote:

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

[JWG]

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

[Ron Rosenfeld]

On Thu, 8 Jun 2006 09:22:01 -0700, JWG <[email protected]> wrote:

>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