[Solved] Extracting right part of text

Discuss the spreadsheet application
Post Reply
bzw
Posts: 3
Joined: Thu Jan 03, 2019 2:13 am

[Solved] Extracting right part of text

Post by bzw »

I am having trouble figuring out how to extract the right side of a text cell beginning after the last space. Each cell in column A doesn't conform to a specific length. I tried the following (attachment) but I get inconsistent results.
Thanks in advance
Bruce
Attachments
open office ex.png
open office ex.png (5.17 KiB) Viewed 4200 times
Last edited by Hagar Delest on Thu Jan 03, 2019 9:24 am, edited 1 time in total.
Reason: tagged solved
windows 10 OpenOffice 4.1.5
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Extracting right part of text

Post by robleyd »

Find the position of the last space in the string and use that in conjunction with MID to extract the text you want.

See also Splitting Full Name into F|M|L which is essentially the same problem.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
bzw
Posts: 3
Joined: Thu Jan 03, 2019 2:13 am

Re: Extracting right part of text

Post by bzw »

Would you be so kind as to give me an example?
windows 10 OpenOffice 4.1.5
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Extracting right part of text

Post by robleyd »

Column B in the attached sample demonstrates finding the last space, using the example from the link I posted; Column C uses that result in MID to get the desired result.

Combining the two into one formula is left as an exercise for the student ;)
after_last_space.ods
(8.75 KiB) Downloaded 301 times
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
bzw
Posts: 3
Joined: Thu Jan 03, 2019 2:13 am

Re: Extracting right part of text

Post by bzw »

Wonderful. Thank you very much. I appreciate it.
Bruce
windows 10 OpenOffice 4.1.5
User avatar
Mohawk
Posts: 83
Joined: Tue May 31, 2011 8:16 pm

Re: Extracting right part of text

Post by Mohawk »

robleyd wrote:Column B in the attached sample demonstrates finding the last space, using the example from the link I posted; Column C uses that result in MID to get the desired result.

Combining the two into one formula is left as an exercise for the student ;)
after_last_space.ods
I believe the following is an improvement. For one thing, the given MID function crops the output string if it happens to be longer than 10 chars, so the RIGHT function is a better fit. Also, as the intermediate calculation is only used once, it is no less efficient to combine both into one formula. However, the concept of substituting a unique character is novel to me.

Assuming the input is Col A:

Code: Select all

=RIGHT(A1;LEN(A1)-FIND("☃";SUBSTITUTE(A1;" ";"☃";LEN(A1)-LEN(SUBSTITUTE(A1;" ";"")))))
...However, this results in an error if there are no spaces at all, so if that is a possibility in your application it needs a revision to:

Code: Select all

=IF(ISERROR(FIND(" ";A1));A1;RIGHT(A1;LEN(A1)-FIND("☃";SUBSTITUTE(A1;" ";"☃";LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))))))
OpenOffice 4.1.2 on Win7/64, 4.1.10 Linux Mint 20.3
User avatar
lader
Posts: 46
Joined: Mon Jul 02, 2018 6:10 pm

Re: [Solved] Extracting right part of text

Post by lader »

For LibreOffice:

Code: Select all

=REGEX(A1; "(.*)(\s)(\S+)$"; "$3"; "g")
LibreOffice 7.6.4.1 on Ubuntu 20.04.4 LTS
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Extracting right part of text

Post by Lupp »

Slightly simplified:

Code: Select all

=REGEX($A4;"[^ ]*$")
And if you prefer to get the #N/A error message if there is no space at all:

Code: Select all

=REGEX($A4;"(?<= )[^ ]*$")
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Mohawk
Posts: 83
Joined: Tue May 31, 2011 8:16 pm

Re: [Solved] Extracting right part of text

Post by Mohawk »

lader wrote:For LibreOffice:
Very good, but unless that works in OpenOffice it's irrelevant on this forum.
OpenOffice 4.1.2 on Win7/64, 4.1.10 Linux Mint 20.3
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Extracting right part of text

Post by RoryOF »

This Forum supports both OpenOffice and LibreOffice, along with some less popular applications all using OpenDocument formats.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Mohawk
Posts: 83
Joined: Tue May 31, 2011 8:16 pm

Re: [Solved] Extracting right part of text

Post by Mohawk »

RoryOF wrote:This Forum supports both OpenOffice and LibreOffice
Sorry, I didn't realise that. I use OpenOffice in Win7, and have started using Linux where LibreOffice was pre-installed - I didn't get on with it (now replaced with OpenOffice).

It's a bit odd that the forum name is "Apache OpenOffice" then! I wouldn't have come here looking for support on Libre.
OpenOffice 4.1.2 on Win7/64, 4.1.10 Linux Mint 20.3
Post Reply