0

My formula breaks with cells values input contain those characters ( ' (.

Here's The sample sheet (Sheet "Report", Cells A2, B2, and A5).

Non working cell input example (in Sheet "Data", Column C):

Mark (this doesn't work)

The error:

Unable to parse query string for Function QUERY parameter 2: 
PARSE_ERROR: Encountered " <ID> "t "" at line 1, column 60. 
Was expecting one of: 
"," ... ")" ... "*" ... "+" ... "-" ... "/" ... "%" ... "*" ... 
"/" ... "%" ... "+" ... "-" ... "," ... ")" ... ")" ... ")" ... 
")" ... ")" ... ")" ... ")" ... ")" ... ")" ... ")" ... ")" ... 
")" ... ")" ... ")" ... ")" ... ")" ... ")" ... ")" ... ")" ... 
")" ... ")" ... ")" ... "*" ... "/" ... "%" ... "+" ... "-" ...

Screenshot:

swq

As you can see, the cells with input with parentheses and apostrophes return an error.

Based on this prior solution

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "s "" at line 1, column 2168,

I adapted the formula to this in A5:

=SUBSTITUTE(QUERY(
        Data!B1:E,"SELECT * WHERE 1=1 "
        &IF(A2="All Regions",""," AND LOWER(B) = LOWER('"&A2&"') ")
        &IFS(B2="All Reps","", 
                REGEXMATCH(B2,"(\(|\)|')"),
                " AND LOWER(C) = LOWER('"& 
                SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2, "(", "\("), "'", "♦"), ")", "\)")&"') ") ,1),"♦", "'")

It is returning only the first cell of the validation range.

No error, but more cells are expected.

Screenshot:

x

Why isn't it working?

Thanks for your help!

Lod
  • 657
  • 1
  • 9
  • 30

1 Answers1

1

Try changing single quotation marks with three times double quotation marks. It will help to avoid problems with some special characters:

=QUERY(
        Data!B1:C,"SELECT * WHERE 1=1 "
        &IF(A2="All Regions",""," AND LOWER(B) = LOWER("""&A2&""") ")
        &IF(B2="All Reps",""," AND LOWER(C) = LOWER("""&B2&""") ") ,1)

enter image description here

Martín
  • 7,849
  • 2
  • 3
  • 13
  • Great help! Many thanks. I couldn't see it. I'll check to understand it fully next. Be well! – Lod Feb 20 '23 at 18:54
  • 1
    It's a tricky topic, but not hard to avoid. Try looking for "escape for special characters" or similar. For example: https://sheetsiq.com/google-sheets/formulas/escape-quotes-in-google-sheets/ – Martín Feb 20 '23 at 19:00
  • Thanks again! I tend to get tunnel vision with those :) and always forget it! Bookmarked for similar later use cases! This doc is well explained. – Lod Feb 20 '23 at 19:04