Literal String Search containing Characters

Question

As part of a countif I'm trying to search for a cell contents that may contain {}. It also needs to be constrained to an exact match within the range. So my first hunch is to go with regexmatch but I'm having issues with the {} becoming part of the regex. I basically need a way of escaping the entire string from the cell. Here's what I've been trying as an example:

REGEXMATCH(A2,"(?:^|\s)("&A1&")(?:\s)")

So say A1 is blue{car} and A2 is like a blue{car}, this should match. However I just get a #REF! error as its trying to use the {}as a quantifier. I know you can escape characters in a predetermined string but as A1 could actually be anything I need a general approach. It seems \Q…\E wrapped around "&A1&" would be perfect but unfortunately its unsupported in sheets.

Any assistance with this gratefully received!

Answer 1

Based on What special characters must be escaped in regular expressions? and RE2 reference, you may easily write a function to escape all special chars that must be treated as literal chars in the regex pattern:

=REGEXREPLACE(A1, "[.^$*+?()[{\\|]", "\\$0")

For {car}, it will yield \{car} and will only match {car}. Note that RE2 regex engine is smart enough to parse the braces in {car} as literal braces. The only problem with a non-escaped { will appear if the contents between braces is numeric and is located after a non-quantifyable pattern, say, at the start of a string or after an alternation operator.

There is another problem in your regex: you require whitespace or start of string before {car} and a whitespace after your {car}, but blue{car} has no whitespaces at either end and is not at the start of the string. Please reconsider your requirements and amend the pattern accordingly.