google-sheetslibreoffice-calc

Google sheet : my downloaded sheet in .ods (calc) doesn't recover formulas with REGEXMATC

I my Google sheet I have cell with formulas like this

=IF(REGEXMATCH(K12;"Oui");IF(AND(REGEXMATCH($C12;"Oui"); REGEXMATCH(K12;"Oui"));L$7;L$8);0)

This formala becomes

=SIERREUR(__xludf.dummyfunction("IF(REGEXMATCH(K12,""Oui""),IF(AND(REGEXMATCH($C12,""Oui""), REGEXMATCH(K12,""Oui"")),L$7,L$8),0)");11)

in Libreoffice Calc.

What is wrong ? Is there a mean to fix this problem ?

Solution

  • Your expressions do not contain any regular expression special characters, so you can also use the find() function that exists in both Google Sheets and LibreOffice.

    Modify your formula like this:

    =if(not(iserror(find("Oui"; K12))); if(not(iserror(find("Oui"; $C12))); L$7; L$8); 0)

    If you need actual regular expressions, you will have to edit the formula after each move from Google Sheets to LibreOffice.

    LibreOffice does not have a regexmatch() function, but it has a REGEX() function that can be used to imitate the former. For example:

    =NOT(ISERROR(REGEX("Non Oui Si"; "Oui")))

    ...will give TRUE, while:

    =NOT(ISERROR(REGEX("Dweezil"; "Oui")))

    ...will give FALSE.

    These LibreOffice expressions accomplish the same as regexmatch() in Google Sheets, save for certain differences in regex patterns.

    See: