[Solved] How to find the last word in cell, copy to new cell

[3graygirls]

I have cells that contain peoples names, each word separated by a space. How do I find the last word (name) in a cell and place it in a separate column? There may be only one name.

Examples:

Mary Ann Doolittle
Peter O'Toole
John Wayne
Ralph G. Cramden
Madonna
I. B. Daft
J. Dee Bull
Juan Francisco Henry Pound

Text to column won't work as I want only one new column with just the last name. If there is only one name, e.g. Madonna, that should go in the "last name" column.

CGH

[robleyd]

[Solved] Extracting right part of text leads to a solution for selecting text after the final space in a string.

Combine that with a search for space(s) in the string; if no string is found, display the entire string; otherwise use the formula from ^^link above^^ to extract the text after the space.

[RusselB]

A modification of Ken Johnson's code

Code: Select all

=FIND(CHAR(0);SUBSTITUTE(A1;"\";CHAR(0);LEN(A1)-LEN(SUBSTITUTE(A1;"\";""))))

from my topic at viewtopic.php?f=9&t=74250 would work.
The modification I'd suggest would be to make it

Code: Select all

=MID(A1;IF(ISERR(FIND(CHAR(0);SUBSTITUTE(A1;" ";CHAR(0);LEN(A1)-LEN(SUBSTITUTE(A1;" ";"")))));0;FIND(CHAR(0);SUBSTITUTE(A1;" ";CHAR(0);LEN(A1)-LEN(SUBSTITUTE(A1;" ";"")))))+1;LEN(A1))

I realize this is a fairly long code, and if you want to break it down, you'll need to use helper columns. I basically used helper columns to get the code to work, then combined everything into one.

[3graygirls]

Thank you, RusselB. It worked great on 176 rows. Thank you both for responding quickly. I would never have gotten there.
CGH

[RusselB]

I'm guessing that you stopped at 176 because that was all the data you had to work with, rather than the formula not working for some.
Please note that, per your request, that presumes the person's surname (aka last name) is at the end, and not followed by any additional information like Jr., Sr., III
Also note that it will return a technically incorrect name for those races that put last name first, like the Chinese and Japanese.
You'll have to handle those situations manually on a one by one basis, as I don't know of any way to write a code to handle those exceptions... or at least, not in Calc.

[3graygirls]

Thank you for the caveat. Asian names will be rare, if ever, in this genealogy project, and they'll appear however my source has them. I'll be on the lookout for the Jr., etc. and handle them manually. Pointing out both of these situations is helpful.
CGH

[FJCC]

I am a little late to this party. Here is a somewhat simpler formula

Code: Select all

=MID(A1;SEARCH("[^\s]+$";A1);LEN(A1))

More caveats about last names. Lots of cultures have multi word family names. In Spanish speaking countries, everyone has two family names. Gabriel Garcia Marquez. In Dutch - Vincent Willem van Gogh. In German - Johann Wolfgang von Goethe. Names are a nighmare!