CODEWITHSUNDEEP
openoffice.orgformulasopenoffice-calc
sami
sami

Reputation: 947

Extract last two words in a cell using openoffice

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

Answers (1)

Greg Bacon
Greg Bacon

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

Related Questions