4

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?

player0
  • 99,092
  • 8
  • 51
  • 98
aabujamra
  • 4,034
  • 9
  • 46
  • 93
  • 3
    Same as in my previous answer: there is no lookaround support in RE2, so you cannot use that logic in the RE2 expression. Use `=AND(REGEXMATCH(A1;"word1");NOT(REGEXMATCH(A1;"word2")))` – Wiktor Stribiżew Feb 22 '19 at 21:37
  • thanks. your again. – aabujamra Feb 22 '19 at 21:40
  • 1
    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 that word, it won't work. – Wiktor Stribiżew Feb 22 '19 at 21:40
  • 1
    https://github.com/google/re2/wiki/Syntax – player0 Feb 22 '19 at 21:42
  • Regarding the edits in this question, I have given way on the title, even if I tend to think of all-caps material as a bit shouty. However, I have removed the request to readers of "shining a light on it" as that is rather vague, and is not as readily understandable as "Why does X happen". We have guidance on vague questions [in this discussion](https://meta.stackoverflow.com/questions/284236/why-is-can-someone-help-me-not-an-actual-question), which is worth a read. – halfer Dec 21 '19 at 23:24

2 Answers2

7

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")))
Wiktor Stribiżew
  • 561,645
  • 34
  • 376
  • 476
1

try: 

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

or:

=(REGEXMATCH(A1; "investimentos"))*(REGEXMATCH(A1; "[^fundos]"))
player0
  • 99,092
  • 8
  • 51
  • 98