ansaurus

Question

Extract last two words in a cell using openoffice

Answer 1

+1 A:

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)

Greg Bacon 2009-12-08 15:46:32

thanks for your answer but it gave me an error: #NAME? (Invalid name), please help

sami 2009-12-09 07:52:04

What value does `=SEARCH("[^ ]+ [^ ]+$", "apple orange banana")` give you? With version 3.1.1, I get `7`. Does the value in `A1` have multiple spaces as separators?

Greg Bacon 2009-12-09 14:41:50

=SEARCH("[^ ]+ [^ ]+$", "apple orange banana") gives me the same error i had before. I am using openoffice 2.4. And there are no multiple spaces as separators.

sami 2009-12-09 14:49:47

could the problem be that i'm using openoffice 2.4?

sami 2009-12-10 07:06:44

Probably. I don't have a 2.4 install to use for testing. That's an old version. Any chance of upgrading?

Greg Bacon 2009-12-10 11:32:06

i have upgraded to 3.1.1 and it gave me an error when testing =SEARCH("[^ ]+ [^ ]+$", "apple orange banana") -> Err 508 (Error in bracketing)

sami 2009-12-15 10:36:40

After a copy-and-paste of the formula from your comment, the result is `7`. I could reproduce the Err 508 only by deleting the left parenthesis or adding an extra right parenthesis on the end, but doing both ways produced messageboxes warning about the bad formula. Could there have been a copy-and-paste problem?In Tools > Options > OpenOffice.org Calc > Calculate, make sure "Enable regular expressions in formulas" is checked.

Greg Bacon 2009-12-15 16:21:29

IT WORKED!!! Your formula had to be with semi-colons(;) instead of commas(,) Thanks a lot.

sami 2009-12-17 07:42:15

I'm glad it helped!

Greg Bacon 2009-12-17 12:34:00

ansaurus

tags:

views:

answers:

Extract last two words in a cell using openoffice

related questions