[Solved] Extracting right part of text
[Solved] Extracting right part of text
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
Thanks in advance
Bruce
- Attachments
-
- 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
Reason: tagged solved
windows 10 OpenOffice 4.1.5
Re: Extracting right part of text
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.
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
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Re: Extracting right part of text
Would you be so kind as to give me an example?
windows 10 OpenOffice 4.1.5
Re: Extracting right part of text
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
Combining the two into one formula is left as an exercise for the student
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
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Re: Extracting right part of text
Wonderful. Thank you very much. I appreciate it.
Bruce
Bruce
windows 10 OpenOffice 4.1.5
Re: Extracting right part of text
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.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
Assuming the input is Col A:
Code: Select all
=RIGHT(A1;LEN(A1)-FIND("☃";SUBSTITUTE(A1;" ";"☃";LEN(A1)-LEN(SUBSTITUTE(A1;" ";"")))))
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
Re: [Solved] Extracting right part of text
For LibreOffice:
Code: Select all
=REGEX(A1; "(.*)(\s)(\S+)$"; "$3"; "g")
LibreOffice 7.6.4.1 on Ubuntu 20.04.4 LTS
Re: [Solved] Extracting right part of text
Slightly simplified:And if you prefer to get the #N/A error message if there is no space at all:
Code: Select all
=REGEX($A4;"[^ ]*$")
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
---
Lupp from München
Re: [Solved] Extracting right part of text
Very good, but unless that works in OpenOffice it's irrelevant on this forum.lader wrote:For LibreOffice:
OpenOffice 4.1.2 on Win7/64, 4.1.10 Linux Mint 20.3
Re: [Solved] Extracting right part of text
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
Re: [Solved] Extracting right part of text
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).RoryOF wrote:This Forum supports both OpenOffice and LibreOffice
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