Ask Your Question

How can I find a string starting from right to left

asked 2016-01-27 09:26:34 +0200

erwin gravatar image

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?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2016-01-27 14:48:00 +0200

karolus gravatar image


=MID( A1 ; SEARCH("\w+\W*?$" ; A1) ;99)

may be turn on: →Tools→Options→Calc→Calculate→→[x]enable regular Expressions in Formulas


edit flag offensive delete link more


@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 :)


pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-01-27 19:48:06 +0200 )edit


With \W*? the RE does also match if there is some Punctuation or whitespace between last word and EndOfString

karolus gravatar imagekarolus ( 2016-01-28 01:15:59 +0200 )edit

@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?


pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-01-28 07:30:21 +0200 )edit

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 gravatar imageoweng ( 2016-01-28 10:33:58 +0200 )edit

@oweng yes of course, but the question was "get the last word", reason why @karolus and I have proposed to use \ w


pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-01-28 11:09:48 +0200 )edit

Ah, of course. Thanks for the clarification. I missed that in the question.

oweng gravatar imageoweng ( 2016-01-28 12:06:00 +0200 )edit

@pierre-yves samyn
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*

karolus gravatar imagekarolus ( 2016-01-28 12:28:27 +0200 )edit

answered 2016-01-28 10:26:28 +0200

oweng gravatar image

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: "[ ][^ ]*$".

edit flag offensive delete link more

answered 2018-07-22 03:07:35 +0200

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 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!

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-01-27 09:26:34 +0200

Seen: 1,904 times

Last updated: Jul 22 '18