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.

3 Comments

  1. HelpfulCommenter

    Double quotes can be escaped by another double quote, as in Visual Basic.

    I tried this with your example, but the comment syntax keeps trying to make an actual link out of it. So here’s another simple example:
    =CONCATENATE(“These are “, “”””, “double quotes”, “”””)
    results in: These are “double quotes”.

    Having four double quotes in a row looks admittedly somewhat disconcerting, but it works.

Leave Comment

Your email address will not be published. Required fields are marked *