Tip of the week #3: How to escape double quotes in a Google Spreadsheet?

If you are using functions in Google Spreadsheet, you might had the same issue that I did: trying to escape a double-quote. Meaning that you wanted to end result of the cell where yuo used the function to have double-quotes at specific points. For example, in one column I have URLs and in another titles of the pages and I want to generate a list of HTML Links from them, like this

<a href=”http:www.example.com”>title</a>

Your code to this would look something like this:

=CONCATENATE(“<li><a href=”, A2, “>”, B2, “</a></li>”)

This is fine and legal HTML, but what if you do want the doublequotes around the URL? I tried all the escape characters I know from various languages, i.e. placing special characters before the quote, e.g. “”, “, ‘”, but none oif these worked and the documentation didn’t help either. What I discovered is that I can have the desired result if instead of trying to escape the ” character I insert it by using the CHAR(34) code. So the code would look like this:

=CONCATENATE(“<li><a href=”, CHAR(34), A2, CHAR(34), “>”, B2, “</a></li>”)

Voila, this is it. I hope this was helpful for you too.