How to extract the last word

[Chris Barnes]

Ok, this seems like it should be an easy thing to do.
Let's say I have in column A a person's full name (assume no Jr's or
III's). In column B, I want that person's LAST name.

I have looked for a function to get the RIGHT-most "word", but I can't
seem to find it.

Am I just blind?



--

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
Chris Barnes AOL IM: CNBarnes
(e-mail address removed) Yahoo IM: chrisnbarnes
Computer Systems Manager ph: 979-845-7801
Department of Physics fax: 979-845-2590
Texas A&M University

 

[Chip Pearson]

Chris,

Try the following formula

=MID(A1,FIND(" ",A1)+1,LEN(A1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Chris Barnes]

Chris,

Try the following formula

=MID(A1,FIND(" ",A1)+1,LEN(A1))

Close. That strips the first names out. But about 50% of the names
have a middle name. For those, this formula produces both the middle
and last names.


--

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
Chris Barnes AOL IM: CNBarnes
(e-mail address removed) Yahoo IM: chrisnbarnes
Computer Systems Manager ph: 979-845-7801
Department of Physics fax: 979-845-2590
Texas A&M University

 

[Ron Rosenfeld]

Ok, this seems like it should be an easy thing to do.
Let's say I have in column A a person's full name (assume no Jr's or
III's). In column B, I want that person's LAST name.

I have looked for a function to get the RIGHT-most "word", but I can't
seem to find it.

Am I just blind?

With the full name in A1:

=MID(A1,1+FIND("~",SUBSTITUTE(A1," ","~",
LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),255)


--ron

 

[Fabian]

Chris Barnes hu kiteb:

Ok, this seems like it should be an easy thing to do.
Let's say I have in column A a person's full name (assume no Jr's or
III's). In column B, I want that person's LAST name.

I have looked for a function to get the RIGHT-most "word", but I can't
seem to find it.

Am I just blind?

This idea will choke on many names. Peter van Helsing, for example, as
well as most Korean names. Computer programmers who think they know how
my name is spelt/capitalised/broken up have cost several customers my
account in the past.

 

[Opinicus]

Let's say I have in column A a person's full name (assume
no Jr's or III's). In column B, I want that person's LAST
name.
I have looked for a function to get the RIGHT-most "word",
but I can't seem to find it.

"RIGHT returns the last character or characters in a text
string, based on the number of characters you specify." You
could probably manipulate this so that it extracts the
right-most word if "word" is defined as string of characters
separated by one or more spaces. (I'm pretty sure I did once
in BASIC using the RIGHT$ function.) However I can't imagine
how you'd be able to cope with people who have last names
like "da Vinci".

 

[Opinicus]

With the full name in A1:

=MID(A1,1+FIND("~",SUBSTITUTE(A1," ","~",
LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),255)

Very good. But it yields Leonardo's last name as "Vinci" no
"da Vinci".

 

[Judy Freed]

This might help

http://www.uncc.edu/SYSDEV/HowTos/E...irst and Last Names in an Excel Worksheet.htm


Judy Freed

 

[Chris Barnes]

Very good. But it yields Leonardo's last name as "Vinci" no
"da Vinci".

Sure, there will always be exceptions that have to be accounted for.
But those are rare enough that I can do those by hand. I'm only talking
about a list of 100+/- names, not thousands. Enough I don't want to
type ALL of them by hand, but few enough that the exceptions will stand
out.

The one Ron gave me works well enough for my purposes.


--

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
Chris Barnes AOL IM: CNBarnes
(e-mail address removed) Yahoo IM: chrisnbarnes
Computer Systems Manager ph: 979-845-7801
Department of Physics fax: 979-845-2590
Texas A&M University

 

[Ron Rosenfeld]

Very good. But it yields Leonardo's last name as "Vinci" no
"da Vinci".

Look at the title of this thread, as well as the OP's initial message, and you
will realize that he was not requesting a routine to parse all possible naming
conventions.


--ron