Regex AND operator in Google Sheets

Question

You can use the pipe | to represent OR and it works fine in Google Sheets. But I want an AND operator and can't find that anywhere. The closest post I found was talking about java application but it doesn't run properly on Google Sheets

I want to check if in the string "equivalencia de estudo patrimonial" the strings "equivalencia" and "patrimonial" are present.

I've already tried:

=regexmatch("equivalencia de estudo patrimonial";"(?:equivalencia)(?:patrimonial)")
=regexmatch("equivalencia de estudo patrimonial";"(?=equivalencia)(?=patrimonial)")
=regexmatch("equivalencia de estudo patrimonial";"/(equivalencia)(patrimonial)/")
=regexmatch("equivalencia de estudo patrimonial";"(equivalencia)(patrimonial)")

No one worked. Always getting false.

Can someone help here?

`=regexmatch("equivalencia de estudo patrimonial";"equivalencia.*patrimonial|patrimonial.*equivalencia")`? — Wiktor Stribiżew, Feb 22 '19 at 21:09
great it works!! \o/ there isn't a simpler way to do it like the pipe for OR right? — aabujamra, Feb 22 '19 at 21:12
Something like `=AND(regexmatch(A1;"\bequivalencia\b"); regexmatch(A1; "\bpatrimonial\b"))` maybe? I added word boundary `\b` to add sense to the `regexmatch` function here. — Wiktor Stribiżew, Feb 22 '19 at 21:17
good functional solutions. if you'd like to post it, the answer is yours — aabujamra, Feb 22 '19 at 21:18

score 2 · Accepted Answer · answered Feb 22 '19 at 21:21

Unfortunately, RE2 library that powers Google Sheets does not support lookarounds.

What you may do is match a combination of word1.*word2 or word2.*word1 with a regex like

=REGEXMATCH("equivalencia de estudo patrimonial"; "equivalencia.*patrimonial|patrimonial.*equivalencia")

Here, equivalencia.*patrimonial|patrimonial.*equivalencia matches equivalencia, some 0+ chars, patrimonial, or (|) patrimonial.*equivalencia matches patrimonial, then any 0+ chars and then equivalencia. It is not that efficient due to the .* part between the two words.

Another solution is to use the AND function and run REGEXMATCH twice:

=AND(REGEXMATCH(A1;"equivalencia"); REGEXMATCH(A1; "patrimonial"))

Regex AND operator in Google Sheets

1 Answers1