6

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
  • 124,011
  • 12
  • 67
  • 124
aabujamra
  • 4,494
  • 13
  • 51
  • 101
  • 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

10

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
  • 607,720
  • 39
  • 448
  • 563
2

try: 

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

or:

=(REGEXMATCH(A1; "investimentos"))*(REGEXMATCH(A1; "[^fundos]"))
player0
  • 124,011
  • 12
  • 67
  • 124
  • Hi @player0 is it possible to do the same with a query formula while keeping the blank cels vertically? I have this formula that works but it filters intermediary blank cells: `=QUERY(B10:B,"SELECT B WHERE B MATCHES '.+(\.|\?|\!|\.\.\.)[\sA-Z].+|.+[a-z(\.|\?|\!|\.\.\.)]$' AND NOT B MATCHES 'Zn(\.|\?|\!|\.\.\.)[\sA-Z].+|.+[a-z(\.|\?|\!|\.\.\.)]$'",0)` – Lod Sep 02 '23 at 00:44
  • This one is equivalent to the Query formula above and gives the expected result with intermediary blank cells `=IFNA(ArrayFormula( IFS(ISNUMBER(B10:B),"", REGEXMATCH(B10:B,".+(\.|\?|\!|\.\.\.)[\sA-Z].+|(.+\.)$")=FALSE,"", (REGEXMATCH(B10:B,".+(\.|\?|\!|\.\.\.)[\sA-Z].+|(.+\.)$")) * (NOT(REGEXMATCH(B10:B,"Zn(\.|\?|\!|\.\.\.)[\sA-Z].+"))),B10:B)),"")` But I'd prefer using the Query formula if possible. – Lod Sep 02 '23 at 00:44