What's the proper way to handle single quotes in Google Sheets QUERY function?

Other Questions Google Sheets
1

I’m having trouble with a QUERY formula that includes text with an apostrophe. Here’s what I’m working with:

=QUERY(QUERY(IMPORTRANGE("spreadsheet_id", "Sheet1!A1:D"),"SELECT * WHERE Col3 = 'Men's clothing: shirts'"),"SELECT Col1, Col3")

I tried doubling the apostrophe but it didn’t work:

=QUERY(QUERY(IMPORTRANGE("spreadsheet_id", "Sheet1!A1:D"),"SELECT * WHERE Col3 = 'Men''s clothing: shirts'"),"SELECT Col1, Col3")

Also attempted using a backslash with no success:

=QUERY(QUERY(IMPORTRANGE("spreadsheet_id", "Sheet1!A1:D"),"SELECT * WHERE Col3 = 'Men\'s clothing: shirts'"),"SELECT Col1, Col3")

My document uses UK locale settings. What’s the correct syntax for escaping apostrophes in this situation?

2

For UK settings, you need semicolons instead of commas in your QUERY function. Your doubled apostrophe approach works, but the syntax needs fixing. Try this:

=QUERY(QUERY(IMPORTRANGE("spreadsheet_id"; "Sheet1!A1:D");"SELECT * WHERE Col3 = 'Men''s clothing: shirts'");"SELECT Col1; Col3")

See how semicolons replace all the commas? That’s because UK locales use commas for decimals. I ran into this exact problem last year - switching to semicolons fixed it instantly. The double apostrophe trick works perfectly once you get the regional formatting right.

3

The issue you’re experiencing with the QUERY function is quite common when using apostrophes in your criteria. The best solution is to double the apostrophe within the string. However, if that isn’t working for you, another effective method is to place the text ‘Men’s clothing: shirts’ in a separate cell, such as A1, and then refer to it in your QUERY. You can modify your formula to something like:

=QUERY(QUERY(IMPORTRANGE("spreadsheet_id", "Sheet1!A1:D"), "SELECT * WHERE Col3 = '" & A1 & "'"), "SELECT Col1, Col3")

This way, you avoid complications with quotes and can update the search criteria easily.

4

Use CHAR(39) instead of typing the apostrophe directly. Something like =QUERY(IMPORTRANGE("spreadsheet_id"; "Sheet1!A1:D");"SELECT * WHERE Col3 = 'Men" & CHAR(39) & "s clothing: shirts'") works when normal escaping doesn’t. Fixed this exact problem for me before.