Search code examples
regexlibreofficelibreoffice-calc

Validate LibreOffice cell contents against a REGEX

I need to enforce a column cell validity against a REGEX.

So I ask if it is possible to validate a cell (really all cells in a column) agaist a REGEX.
I see we have data validity custom formula option, but I can't find any docs about how to use it to validate against a REGEX...

Can anybody help?

Solution

  • The condition worked for me Formula is

    AND(ISERROR(REGEX(E9;"^[0-9]{11}$"));ISERROR(REGEX(E9;"^[A-Za-z]{6}[0-9]{2}[A-Za-z]{1}[0-9]{2}[A-Za-z]{1}[0-9A-Za-z]{3}[A-Za-z]{1}$"));ISERROR(REGEX(E9;"^[A-Za-z]{6}[0-9LMNPQRSTUV]{2}[A-Za-z]{1}[0-9LMNPQRSTUV]{2}[A-Za-z]{1}[0-9LMNPQRSTUV]{3}[A-Za-z]{1}$")))

    Yes, it is very long and difficult to edit. Perhaps, if you think about the problem long enough, you can come up with a shorter expression.

    Long Condition

    Update By the way, Data-Validity can also be implemented by negating the same condition:

    NOT(
  AND(
    ISERROR(REGEX(<Checked cell address>;"^[0-9]{11}$"));
    ISERROR(REGEX(<Checked cell address>;"^[A-Za-z]{6}[0-9]{2}[A-Za-z]{1}[0-9]{2}[A-Za-z]{1}[0-9A-Za-z]{3}[A-Za-z]{1}$"));
    ISERROR(REGEX(<Checked cell address>;"^[A-Za-z]{6}[0-9LMNPQRSTUV]{2}[A-Za-z]{1}[0-9LMNPQRSTUV]{2}[A-Za-z]{1}[0-9LMNPQRSTUV]{3}[A-Za-z]{1}$"))
  )
)

    Custom Validity