-2

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?

MarcoS
  • 17,323
  • 24
  • 96
  • 174
  • I also don't know how to constrain input to a cell using `REGEX()`. Do you think that conditional formatting with bright selection of cells that do not match `REGEX()` will not be enough? – JohnSUN Jan 08 '22 at 17:40
  • @JohnSUN: yes, I think it could be enough, definitely... If not even better, for some use cases... But, how to realize it? – MarcoS Jan 08 '22 at 17:53

1 Answers1

1

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

JohnSUN
  • 2,268
  • 2
  • 5
  • 12
  • Almost perfect! But, what if I want to enforce validity on a range of cells? Can I use instead of ? – MarcoS Jan 10 '22 at 04:15
  • Yes, this is what is meant by ``. [**Data-Validity**](https://help.libreoffice.org/7.2/en-US/text/scalc/01/12120100.html) is a link to Help. Take a look at the description of `Custom`. The second paragraph describes exactly how to apply this to an entire range of cells. – JohnSUN Jan 10 '22 at 07:01
  • Thanks. The problem for me (LibreOffice 6.4.6.2) is nor data/validaty/criteria/custom nor format/condition/conditional/formula do work with regex... it is simply ignored by validation or conditional syle... In options/Calc/Calculate I did also check "Allow REGEX in formulas"... Nothing works for me... :-( – MarcoS Jan 10 '22 at 09:09
  • **6.4.6.2**?! Oh yes, it really is a problem. Well, it means conditional formatting (or a software update). Or a helper column with the same formula that will show that the value matches the mask. – JohnSUN Jan 10 '22 at 09:12
  • I do not really fully understand your comment. However I did upgrade to 7.2.4.2, but no change for me... – MarcoS Jan 10 '22 at 15:40
  • It might make sense to move this discussion to [**ask.libreoffice**](https://ask.libreoffice.org/) - there are no restrictions on file sharing with sample data and examples. – JohnSUN Jan 10 '22 at 16:07