drug interaction list

How to : Add Quotes with Excel Concatenate function

Posted on 07. May, 2009 by in Computers, Howto

I use excel to create the HTML code for large tables.

Whenever I need to publish a large table, I first  type it out in excel.
Then I open the next sheet, create my HTML code using the concatenate function.
Its a neat trick that has always worked well.
I just manage the data (in one sheet) and the code (in the other sheet) is managed on its own.

The only trick is to be able the add ” symbol on to the hyperlinks.

To create one ” symbol, you will need four “s in your code.
=concatenate(“”””,”A”,””””) produces the output “A”

So, now you know, even if you didn’t know already…

11 Responses to “How to : Add Quotes with Excel Concatenate function”

  1. Joel

    28. Jan, 2010

    Perfect! This is exactly the hint I needed. Thanks.

  2. Gayathri

    03. Nov, 2010

    Thanks, was just the thing I was trawling the web for!

  3. Jean-Paul

    28. Apr, 2011

    Good but even more trick when there is also a mix of text inside.

    I wanted to have this, with content of Cell A2 this MQL formula:

    disconnect bus “Business Unit” “Latches Division” 0000000003 rel Division to “VCS Design Organization” “A2″ -;

    So I had to put the following Excel formula:

    =”disconnect bus “”Business Unit”” “”Latches Division”” 0000000003 rel Division to “”VCS Design Organization”” “&””””&A2&””””&” -;”

  4. Jim

    02. Jun, 2011

    I would be interested in knowing WHY this works. Intuitively, it doesn’t make sense that four double-quote symbols will render a one double-quote symbol. So, is it special code in Excel’s core? Otherwise, I can’t make logical sense of why it would work…

  5. Dan

    28. Oct, 2011

    Actually not that hard, the actual special excel code is the double “” quote to create the ” in a line of text. It’s similar to how other programing languages deal with special characters, usually an escape character prior to the character you want.

    Now the “”””, it’s
    “(for start of text) “”(to create the “) “(end of text)

  6. Shahin

    23. Jan, 2012

    Thank you so much for helping me understand how to use the concatenate function in excel. Take care and all the best!

  7. Dave

    02. Feb, 2012

    OK, one more variation: I needed to have quotes around the contents of another cell. In this case, it took *three* sets of quotes in the concatenate function, like this:


    If K7 contained something like _text_, the output would be:


  8. George

    18. Feb, 2012

    This is not working for me. Here is my formula.

    =CONCATENATE(“some text “,””””,B9,””””,” more text”)

    If cell B9 contains a string I want the result to be

    some text “string” more text

    Tried this in both Excel 2003 and 2010. I can’t seem to find a way to insert the quotation marks ” symbol.

    Searched the Internet, got lots of hits that four ” symbols translates to a single ” symbol in the CONCATENATE function. Also tried using the simpler form using the ampersign & operator. No go.

  9. Krishnan

    18. Feb, 2012

    I copy/pasted your formula into excel and it works like a charm to me.

  10. George

    20. Feb, 2012

    It seems that a blank (Space character) must follow the comma, as in:

    =CONCATENATE(“some text “, ””””, B9, ””””, ” more text”)

    Very subtle, and totally unexpected requirement. Here it is again this time using the underscore _ character to emphasize the location of the blank (space) character:

    =CONCATENATE(“some text “,_””””,_B9,_””””,_” more text”)

    I thought this kind of dumb syntax was eliminated from programming techniques a couple of decades ago!

  11. Marie R Esposito

    17. Jan, 2013

    This also applied to concatenating cells containing apostrophes ( single quotes) with text cells. Thanks

Leave a Reply

%d bloggers like this: