Search code examples
regexgoogle-sheetsgoogle-sheets-formulare2

Regex "NOT" in Google Sheets (RE2)

I want to check if in a cell there is one word but not another. In this post there was some buzz around this matter but the elected solution included a script function. There has to be an easier way to do that.

I want to check if in the string "investimentos" the string "investimentos" is present and "fundos" is not.

I've already tried those below (disclaimer: I'm a beginner with regex):

=regexmatch("investimentos";"(investimentos)^(fundos)")
=regexmatch("investimentos";"(investimentos).*^(fundos)")
=regexmatch("investimentos";"(investimentos)(^fundos)")
=regexmatch("investimentos";"(investimentos).*(^fundos)")

I'm always getting false. Why is that?

Solution

  • There is no lookaround support in RE2, so you cannot use the common logic to match one string excluding another.

    You could do that with a single regex if the negated value is just 1 character. Like ^[^I]*GO[^I]*$ will match a string that has no I but contains GO, but in case there are multiple chars in the word you want to exclude, it won't work.

    Use

    =AND(REGEXMATCH(A1;"investimentos");NOT(REGEXMATCH(A1;"fundos")))