How to : Add Quotes with Excel Concatenate function

Posted on 07. May, 2009 by Krishnan 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.
eg:
=concatenate(“”"”,”A”,”"”") produces the output “A”

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

Joel

28. Jan, 2010

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

03. Nov, 2010

Thanks, was just the thing I was trawling the web for!
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&”"”"&” -;”
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…
Dan

28. Oct, 2011

@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)
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!
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:

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

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

blahblah_”text”_blahblah
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.
Krishnan

18. Feb, 2012

I copy/pasted your formula into excel and it works like a charm to me.
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!