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*.

CodePudding user response：

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.