Page 1 of 1
[Solved] Extracting right part of text
Posted: Thu Jan 03, 2019 2:32 am
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
Re: Extracting right part of text
Posted: Thu Jan 03, 2019 4:01 am
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.
Re: Extracting right part of text
Posted: Thu Jan 03, 2019 5:10 am
by bzw
Would you be so kind as to give me an example?
Re: Extracting right part of text
Posted: Thu Jan 03, 2019 7:10 am
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
Re: Extracting right part of text
Posted: Thu Jan 03, 2019 7:18 am
by bzw
Wonderful. Thank you very much. I appreciate it.
Bruce
Re: Extracting right part of text
Posted: Fri Nov 01, 2019 12:59 pm
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;" ";""))))))
Re: [Solved] Extracting right part of text
Posted: Fri Nov 01, 2019 7:04 pm
by lader
For LibreOffice:
Code: Select all
=REGEX(A1; "(.*)(\s)(\S+)$"; "$3"; "g")
Re: [Solved] Extracting right part of text
Posted: Fri Nov 01, 2019 7:37 pm
by Lupp
Slightly simplified:
And if you prefer to get the
#N/A error message if there is no space at all:
Re: [Solved] Extracting right part of text
Posted: Mon Aug 02, 2021 6:47 pm
by Mohawk
lader wrote:For LibreOffice:
Very good, but unless that works in OpenOffice it's irrelevant on this forum.
Re: [Solved] Extracting right part of text
Posted: Mon Aug 02, 2021 6:54 pm
by RoryOF
This Forum supports both OpenOffice and LibreOffice, along with some less popular applications all using OpenDocument formats.
Re: [Solved] Extracting right part of text
Posted: Mon Aug 02, 2021 7:05 pm
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.