I have a Google Spreadsheet with two tabs - one containing regexes and values:
And the other containing phrases which matches regex in the first tab, and a
QUERY Formula that picks the right value from the regex column for each matching phrase:
=QUERY(Regex!A:B, "SELECT B WHERE lower('"&A1&"') CONTAINS A LIMIT 1")
Winter in California and
Summer in californiaaaaa both match '.california.', and matched with the
The problem is that if the matched cell contains a single quote, it breaks the parsing of the query.
For unrelated reasons, I can't add columns to the table - it is being downloaded as a CSV by an external service and its format can't be changed.
What Have I Tried
- REGEXREPLACE between the
&&that replaces the single quote with an empty string
- Reviewing the manual
- Using CONTAINS, which suffer from the same quoting problem
How can I escape single quotes in a cell content in the concat operator (&), in a query?