How to show the last name in Excel?

Question

In an Excel file I have a column with the names:

John Charles Silver

Peter Albert Brown

Julia kelly Williams

How can I make a formula to it create a colums showing the last name?

I mean, it sholud show:

Silver

Brown

Williams

I have this formula:

=LEFT(A1;LOCATE(" ";A1;1)-1)

That show the first name of a colums of data.

What is the formula toi show the last name of a colums of data, in Excel?

Can you help me?

Answer 1

Try this formula:

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

hth

Answer 2

Not sure what version of Excel you're using, since 'LOCATE' doesn't seem to be a real function in XL2003, but:

=RIGHT(A1, FIND(" ",A1)) will return everything until the first space starting from the right side (i.e. the last name).

=LEFT(A1, FIND(" ",A1)) will return everything until the first space starting from the left side (i.e. the first name).

This assumes that the name you're splitting is in A1, of course.

Also, you can use Excel's "Text To Columns" feature in the Data menu:

1. Highlight the column and click Data>Text to Columns

2. Choose 'Delimited' and click Next

3. Uncheck all the boxes except 'Space' and click Finish.

Hope this helps!

Answer 3

Well doesn't look like you are using MS Excel... my guess is using OpenOffice.

Here is the solution, but you have to do the syntax conversion (e.g. FIND to LOCATE, "," to ";"):

= IF(ISNUMBER(FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))), RIGHT(A1,LEN(A1)-(FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))+FIND(" ",A1))), RIGHT(A1,LEN(A1)-FIND(" ",A1)))

Just in case there are other differences between Excel and openoffice, here is how the logic works and you find the answer yourself:



IF( there is space between ( the second part of the full name ))

//the full name is separate by two spaces

starting from the right, length of the full name - (length of the first part + length of the first sub part of the second part)

ELSE

//the full name is separate by two space, print second part of the name

starting from the right, length of the full name - length of the first part

Note: It only works with Names that with/without a middle name. If a name is separate by 3 or more spaces, it will not work.

e.g.

Jennifer Aniston - works

Jennifer Love Hewitt - works

Jennifer Who Are You - don't work