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?
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")))