With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.
Wednesday, December 31, 2014
yogi_Using Query Function When A Item To Be Searched For Contains Quotation Mark
post byScottKI: https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!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?