I have a column with text strings that contain multiple spaces. How can I get the last word?
My idea was to FIND the position of the last space, but I can’t find a FINDB or FINDBACK function.
What cell formula can I use to get the last word of text string?
Hallo
=MID( A1 ; SEARCH("\w+\W*?$" ; A1) ;99)
may be turn on: →Tools→Options→Calc→Calculate→→[x]enable regular Expressions in Formulas
Karolus
@karolus Very nice, but the ? does not seem necessary. Can you explain that?
Moreover, it goes without saying that we should complicate whether to ignore a possible end point 
Regards
Hi
With \W*? the RE does also match if there is some Punctuation or whitespace between last word and EndOfString
@karolus I thought that was your goal, but I still think it’s not necessary: \W matches characters that separate words: whitespace characters, punctuation marks, etc.
I think =MID( A1 ; SEARCH("\w+\W*$" ; A1) ;99) gives the same result but there may be some cases that I do not see?
Regards
The provided regex seems to return the entire string after the first SPACE, rather than the string after the last SPACE i.e., given the string abcd 1234 !@#$ ;:'" <>[] I get 1234 !@#$ ;:'" <>[], rather than <>[].
@oweng yes of course, but the question was “get the last word”, reason why @karolus and I have proposed to use \ w
Regards
Ah, of course. Thanks for the clarification. I missed that in the question.
@PYS
It seems you are right - because we use the opposite subsets \w versus \W , there is no difference between non-greedy \W*? and greedy \W*
I prefer a back to basics type of regular expression:
=MID(A1;SEARCH("[^ ]*$";A1;1);LEN(A1)-SEARCH("[ ][^ ]*$";A1;1))
i.e., find the last non-SPACE character in the line: "[^ ]*$"; obtain the length of string: LEN(A1); subtract from the length of string the position of the last SPACE followed by non-SPACE character: "[ ][^ ]*$".
I’ve tried all these things (and quite a number of others found elsewhere) but I can’t get any of them to work in LibreOffice 6.0.5.2. I am specifically trying to remove all but the first letter of the last word in every cell (they have different numbers of words however.) Can anyone help me? Thanks in advance!
FINDBACK() is a little awkward to implement without regex in a plain formula, but not impossible.
Here’s how the final formula is built up, exploded in digestable steps:
First find how many non-spaces there are:
=LEN(SUBSTITUTE(A1," “,”"))
Find how many spaces there are:
=LEN(A1)-LEN(SUBSTITUTE(A1," “,”"))
Replace the space at the [number of spaces]-th occurrence with a marker (choose an unused character):
=SUBSTITUTE(A1," “,“☿”,(LEN(A1)-LEN(SUBSTITUTE(A1,” “,”"))))
Find the index of the marker:
=FIND(“☿”,SUBSTITUTE(A1,“0”,“☿”,(LEN(A1)-LEN(SUBSTITUTE(A1,“0”,"")))))
Credits: https://stackoverflow.com/a/25820019/1052284 and yours truly
Note that this crashes with “Err:502” if there are no spaces. To cover such cases it gets even more unwieldy:
=IF(LEN(SUBSTITUTE(A1," “,”"))=LEN(A1),LEN(A1),FIND(“☿”,SUBSTITUTE(A1," “,“☿”,(LEN(A1)-LEN(SUBSTITUTE(A1,” “,”"))))))
The question was to get the last word, not to get the index of last space before it (and your formula will not find the last word, if it is followed by spaces). The idea to FIND the position in the question was just an imagined means to solve that…
and it all is implemented very simply using REGEX available since v.6.2:
=REGEX(A1;"\b\w+\s*$")