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

Discuss the spreadsheet application

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

Postby 3graygirls » Tue Jul 09, 2019 1:28 am

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
Last edited by 3graygirls on Tue Jul 09, 2019 2:32 am, edited 1 time in total.
LibreOffice Calc on Windows 7
Version: 6.2.4.2 (x64)
3graygirls
 
Posts: 14
Joined: Wed Jun 19, 2019 2:49 am

Re: How do I find the last word in cell and copy to a new c

Postby robleyd » Tue Jul 09, 2019 1:53 am

[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.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2873
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: How do I find the last word in cell and copy to a new c

Postby RusselB » Tue Jul 09, 2019 2:03 am

A modification of Ken Johnson's code
Code: Select all   Expand viewCollapse view
=FIND(CHAR(0);SUBSTITUTE(A1;"\";CHAR(0);LEN(A1)-LEN(SUBSTITUTE(A1;"\";""))))
from my topic at https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=74250 would work.
The modification I'd suggest would be to make it
Code: Select all   Expand viewCollapse view
=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.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5288
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: How do I find the last word in cell and copy to a new c

Postby 3graygirls » Tue Jul 09, 2019 2:25 am

Thank you, RusselB. It worked great on 176 rows. Thank you both for responding quickly. I would never have gotten there.
CGH
LibreOffice Calc on Windows 7
Version: 6.2.4.2 (x64)
3graygirls
 
Posts: 14
Joined: Wed Jun 19, 2019 2:49 am

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

Postby RusselB » Tue Jul 09, 2019 2:42 am

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.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5288
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Postby 3graygirls » Tue Jul 09, 2019 2:51 am

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
LibreOffice Calc on Windows 7
Version: 6.2.4.2 (x64)
3graygirls
 
Posts: 14
Joined: Wed Jun 19, 2019 2:49 am

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

Postby FJCC » Tue Jul 09, 2019 4:27 am

I am a little late to this party. Here is a somewhat simpler formula
Code: Select all   Expand viewCollapse view
=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!
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7224
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 30 guests