Conditional formatting a range of cells against a multiple REGEX

English
#1

I need to conditional formatting a range of cells against a multiple REGEX.
In particular, I need to conditional formatting all cells in A column, since A3.
My REGEX is a 3 conditions REGEX, and I should accept input if ANY of the 3 is matched, and conditionaly format output in case neither of the conditions is matched.
The conditions are:

^[0-9]{11}$
^[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}$
^[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}$

(This is the specification to identify a company according to Italian law).

Since I did just learn function names should be translated, I specify I use Italian version of Libreoffice.

I am trying something like:
E( VAL.ERRORE(ESPR.REG(A3:A1048576;"^[0-9]{11}$")); VAL.ERRORE(ESPR.REG(A3:A1048576;"^[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}$"")); VAL.ERRORE(ESPR.REG(A3:A1048576;"^^[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}$")); )
but I get no error, nor any conditional formatting for not matching inputs… :frowning:

#2

You have to test your formula in a visible cell on sheet. Failing conditional formats do not raise any error.

#3

No, my friend, no! The first parameter to ESPR.REG() is a single cell, not a whole range! Check out my file from the previous discussion: EVERY cell is formatted with a condition where ONLY THIS cell is mentioned.

#4

Thanks!!!
But in your file from the previous discussion, I can’t see the conditional/condition, and it’s greyed-out.
And, how to apply the SAME condition to all cells in a column? Should I repeat the formula once for any cell??? :slight_smile:

#5

Try Format-Conditional-Manage… and Edit

EditCF
EditCF375Ă—537 22.7 KB

While this may sound difficult, it is actually very simple. Indeed, create a conditional format for one cell (check it works correctly). Now copy this format with the Clone tool
CloneFormat
or using Paste Special-Format

#6

Like the references in any formula expression you write into a cell, conditional formatting follows the rules of absolute and relative addressing.
https://forum.openoffice.org/en/forum/viewtopic.php?f=75&t=84265

#7

I’m getting mad… :frowning:

I’m trying to apply conditional formatting to ONE cell: “A3” (I postpone the problem to apply to many cells).
This single regex does not work (no conditional formatting ever):
VAL.ERRORE(ESPR.REG(A3;"^[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}$"))
A regular input is for example: ABCDEF12G34H567Z.
An error input (conditional formatting) is for example: 123.

Why??? Please, help…

#12

Are you missing any of these steps?

CF_REGEX

#14

Thanks so much!

After configuring to use English on formulas, I could finally let it work…

You were really helpful and friendly!

This is one of the best forums I’ve ever encountered, for one of the most messy piece of software ever! :wink:

#15

What exactly “did not work” in the spreadsheet file I attached yesterday? It does the conditional formatting right. The invalid entries are displayed in red font (style “Warning”). If that “does not work” for you, I would think that you run some 6.x version of LibreOffice.
The screenshot attached to the document shows how I did the trick. It is a mixed reference to the cell in column A and an absolute reference to the regex. Keeping the regex in a separate cell makes debugging a lot easiser.

#8

t72586.ods (44.8 KB)

#9

It’s not working for me.
I think it’s not acceptable to mess up with such stuff to impose a validation condition on a value…
It was very kind of you, but I find libreoffice environment absolutely confusing, error prone, undebuggable, and I’d say irrational. The fact function names change based on the language of the user is completely mad, too, according to me…
I’m giving up. Thanks for your support.

#10

“It’s not working” is not a valid problem description". The uploaded file formats the strings in column A correctly. Column B shows the results of the REGEX function. Column E shows the result of the test.

Version: 7.2.4.1 / LibreOffice Community
Build ID: 27d75539669ac387bb498e35313b970b7fe9c4f9
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: x11
Locale: de-DE (de_DE.UTF-8); UI: en-US
Calc: threaded

#13

You are right, I also perceive it as madness …

But there is good news: the LibreOffice developers have provided a “pill for spoiled nerves”. It’s in Tools - Options - LibreOffice Calc - Formula.

EnglishFormula
EnglishFormula740Ă—498 23.9 KB

#11

Storing strings in an arithmetic calculator is a foolish idea anyway (extremely popular, though).
Open the attached database and embedded input form.
t72586.odb (12.4 KB)

#16

Spreadsheets are used, among other things, for the exchange of information between people (and computers). Dump your databases? :slightly_smiling_face:

#17

Data storage (list keeping) is by far easier to maintain with databases. I dump the result of a database query into some document (mostly Writer, sometimes Calc) when I want to exchange documents.
In this particular case I do not enter invalid strings instead of detecting them afterwards. Same with csv. I avoid importing invalid record sets instead of trying to fix wrongly imported data.

#18

@Villeroy I cannot think of how to implement validation against three patterns using an input mask. Yes, Italian lawmakers put a pig on the programmers when they introduced three different formats for one identifier. But how is such a problem solved in Base? Any ideas?

#20

To be honest, it makes some sense, since the 3 different formats correspond to different entities (persons, italian companies, foreign companies).

However, JohnSUN suggestion is working for me now:

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

#21

As I said two days ago if you think about the problem long enough, you can come up with a shorter expression - @erAck did exactly that (for some reason I didn’t succeed the first time, I had to combine three different REGEX())

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.