Computers, Howto

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…

Advertisements

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

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s