Wednesday, December 31, 2014

yogi_Using Query Function When A Item To Be Searched For Contains Quotation Mark

             Google Spreadsheet   Post  #1872
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Dec-31-2014
post by  ScottKI:!category-topic/docs/spreadsheets/RCXcfDihHfo
Trouble using QUERY with a cell containing a quotation mark
I'm working on an investing spreadsheet. For part of my XIRR formula, I'm using the QUERY command below. Column C contains the name of the stock. Column A contains the transaction dates.

=query(Transactions!A$2:R,"select A WHERE (B='Buy' OR B='Sell' OR B='Div') AND C contains 'Intel' ")

This formula works well for companies like Intel, Apple, Google, etc. However, if the company name contains a quotation mark, as below, it fails.

=query(Transactions!A$2:R,"select A WHERE (B='Buy' OR B='Sell' OR B='Div') AND C contains 'McDonald's' ")

It also runs into the same problem if I reference its cell. How do I let the spreadsheet know that the quote is part of the text that it is supposed to search for?

Thanks for the help!