Reputation: 947
I need a formula to extract the last two words in a cell using openoffice. For example a cell contains the words: "enjoy the rest of your day" I would like to extract "your day" using a formula. I know how to extract the last word:
=RIGHT(A1;LEN(A1)-FIND("*";SUBSTITUTE(A1;" ";"*";LEN(A1)-LEN(SUBSTITUTE(A1;" ";"")))))
which results in "day". But i need a formula for the last two words.
Upvotes: 2
Views: 4574
Reputation: 139701
SEARCH
supports regular expressions, so use
=RIGHT(A1, LEN(A1) - SEARCH("[^ ]+ +[^ ]+$", A1) + 1)
When I use semicolons as below, Calc silently substitutes commas, but the OP reports success entering it this way:
=RIGHT(A1; LEN(A1) - SEARCH("[^ ]+ +[^ ]+$"; A1) + 1)
Upvotes: 1