How to match strings containing special string, but not containing another string with REGEXMATCH

Question

I have a working REGEXMATCH based formula

=ArrayFormula(if( REGEXMATCH(topProdukte!AV2,"^si|si\d+|si-"), topProdukte!AV2, "NO"))

Now I try to add an AND NOT condition and fail on something. I try it with negative lookahead

=ArrayFormula(if( REGEXMATCH(topProdukte!AV2,"^(?!sia)si|si\d+|si-"), topProdukte!AV2, "NO")) as adviced in https://stackoverflow.com/a/3194881/1992004 - but get an error #REF, not valid regex.

Finally I need to match strings containing ^si|si\d+|si-, but not containing *sia*.

Do you mean you do not want to match string that start with `sia`? Then, you'd be able to use `^si(?:[^a]|$)|si\d+|si-`. Else, if `sia` anywhere in the string signals failure, you can't add it to this regex, add a separate check. — Wiktor Stribiżew, Oct 28 '21 at 11:05
could you say how to add a separate check in the same formula? — Evgeniy, Oct 28 '21 at 11:19

score 0 · Accepted Answer · answered Oct 28 '21 at 11:26

You need to do two things:

Makes sure all your regexps match whole string since it is a requirement coming from REGEXMATCH
Add a separate statement checking if sia is not present in the string.

So, a possible solution is

=ArrayFormula(if( AND(NOT(REGEXMATCH(topProdukte!AV2, ".*sia.*")), REGEXMATCH(topProdukte!AV2,"^si.*|.*si\d.*|.*si-.*")), topProdukte!AV2, "NO"))

Here,

AND(...,...) requires both conditions to pass / return true
NOT(REGEXMATCH(topProdukte!AV2, ".*sia.*")) - makes sure the string does NOT contain sia (note the .* on both ends make sure the whole string is consumed)
REGEXMATCH(topProdukte!AV2,"^si.*|.*si\d.*|.*si-.*") - the part you already have: it matches si at the start of string, si+digit or si- anywhere in the string.

How to match strings containing special string, but not containing another string with REGEXMATCH

1 Answers1