How To Return Data Validation values with special characters/non alphanumeric Characters in Query formula in Google Sheets?

Question

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:

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:

Why isn't it working?

Thanks for your help!

Answer 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)