How to : Add Quotes with Excel Concatenate function


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.
eg:
=concatenate(“”””,”A”,””””) produces the output “A”

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

11 thoughts on “How to : Add Quotes with Excel Concatenate function

  1. 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&””””&” -;”

  2. 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…

  3. @Jim
    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)

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

  5. 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:

    =”blahblah_”””&K7&”””_blahblah”

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

    blahblah_”text”_blahblah

  6. 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.

  7. 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!

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

Leave a Reply

%d bloggers like this: