0
Follow
5
View

Validate LibreOffice cell contents against a REGEX

zcc493841274 注册会员
2022-12-20 16:51

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.

By the way, can also be implemented by negating the same condition:

NOT(
  AND(
    ISERROR(REGEX(cell address>;"^[0-9]{11}$"));
    ISERROR(REGEX(;"^[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(;"^[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}$"))
  )
)