Query & importrange with a word that has symbol ' (example = James 'Lee)

0
votes

I have issue to list the training program with the name that has symbol '. The participant name is James 'Lee

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets...", "'record'!A:Z"), "Select Col7,Col18 where Col1 = '"&C5&"' and Col1 is not null", 0)

enter image description here

I received the error message: Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "Lee "" at line 1, column 40. Was expecting one of: "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "and" ... "or" ...

2
google-sheetsgoogle-sheets-formula

2 Answers

2
votes

Try

=QUERY( IMPORTRANGE ("..., "...."), "Select Col7, Col18 where Col1 = """&C5&"""", 0)

and see if that works?

0
votes

Select Col7,Col18 where Col1 like '"&regexreplace(C5,"\'","\%")&"' and Col1 is not null

will remove the hyphen and replace it with a wild card character %.

The side effect is that it will also bring back results like 'James OtherLee'