Regexmatch syntax for Google Sheets

Question

Cell A1 contains:

I finally found the best dance partner!! #ad, check out the link in bio

Cell B1 contains:

=REGEXMATCH(A1,"/#[aA][dD](?![A-z]|[0-9]|-|_|\/|\\)/")

According to different regex testers, the pattern

#[aA][dD](?![A-z]|[0-9]|-|_|\/|\\)

is valid and should match the string in A1. REGEXMATCH should return TRUE, not an error. I've also tried the formula without the opening and trailing slashes to no avail:

=REGEXMATCH(L2,"#[aA][dD](?![A-z]|[0-9]|-|_|\/|\\)")

The error I'm getting in both cases is:

"Function REGEXMATCH parameter 2 value "#[aA]dD" is not a valid regular expression."

I don't know what's wrong with my regex syntax for Google Sheets, and I can't find any documentation by Google or others online.

Answer 1

RE2 library does not support lookaheads. Neither is the regex literal notation with delimiters is supported, the first and last slashes must be removed, or they will be parsed as part of the regex pattern. [A-z] matches more than just letters, you should write it as [A-Za-z] to match any ASCII letter.

Instead of (?![A-z]|[0-9]|-|_|\/|\\) (that is equal to (?![A-Za-z0-9_/\\-])) use (?:$|[^a-z0-9_/\\-]):

=REGEXMATCH(A1,"(?i)#ad(?:$|[^a-z0-9_/\\-])")

The (?i) is the case insensitive modifier, no need to write [aA].

Details

• (?i) - case insensitive mode on
• #ad - #ad substring
• (?:$|[^a-z0-9_/\\-]) - either the end of string ($) or (|) any char other than an alphanumeric, _, /, \ or - char.