mjhjmtu

Excel automatically corrects my formula, saying "We found a typo in your formula and tried to correct it to:"

=CONCAT(CHAR(34),A4,""":""",D4,"""")

My original formula only has three quotation marks at the end:

=CONCAT(CHAR(34),A4,""":""",D4,""")

For your info I combine the data from two columns in Excel and the result shall look like this:

"welcome":"Bienvenue!"

The A4 cell contains: welcome

The D4 cell contains: Bienvenue!

CHAR(34) represents the initial quotation mark for cell A4.

The Excel CONCAT() function combines the data from different Ms Excel cells.

Note: This question is not similar to the Superuser question Excel adds quotation marks to the result of my formula

Quotation marks have a special meaning in Excel, so you have to jump through some hoops to use them as a regular character. You can do that by doubling them, but that can get complicated, depending on the situation.

To avoid terminology confusion, lets ignore apostrophes (sometimes called "single quotes"). Any reference here to "quote" or "quotation mark" refers to the symbol with two tick marks.

The rules are understandable if you recognize what's going on.

• Quotation marks always need to be in pairs (but not necessarily adjacent).


• A doubled quote becomes a single text character (a quotation mark).


• Text needs to be in quotes.


• If the doubled quote is in the middle of a string, it is just another character.


• If it is at the beginning or end of a string, the string of which it is a part must be enclosed in quotes (so you will end up with triple quotes at the beginning or end).


• If it is concatenated as the only character (at the beginning, middle, or end of an expression), it needs to be enclosed in quotes (so you end up with quadruple quotes).

Here are some examples using your formula:

• You can see that you got it right using triple quotes around the colon. Think of that as the colon surrounded by doubled quotes, and all of that inside quotes to delimit that text.


• You got it right again with CHAR(34) to start the string. You can do the same for the last character.


• Since the first and last characters are concatenated, you could alternatively quadruple those (like D4&""""). That's what Excel tried to fix for you.


• If the expression had started or ended with hard-coded text instead of a cell reference, you could use tripled quotes (like """Welcome")

So this can be done using multiple quotes, although you can go cross-eyed if you need to diagnose formula problems if you mess up. The simple, safe way to do it is to use CHAR(34), especially when quadruple quotes are needed, as Hannu's answer suggests (although my own preference would be to keep the triple quotes around the colon to avoid splitting one text expression into three pieces; but that's why there's both vanilla and chocolate ice cream).

These two are equivlent, and both create the expected string:

=CONCAT(CHAR(34),A4,CHAR(34),":",CHAR(34),D4,CHAR(34))

Note that , inside function calls as this may need to be changed to ; depending on your locale.

=CHAR(34) &A4 &CHAR(34) &":" &CHAR(34) &D4 &CHAR(34)

You can include doublequotes in a string of chars as there is "eascaping" available, but combined with the fact that strings start and end with a doublequote it quickly becomes ugly and hard to maintain; e.g. ="""welcome"":""Bienvenue!""" - you need more quotes to add the cell references above; =""""&A4&""":"""&D4&"""" ... easy to get wrong.

NOTE: updated w.r.t. quote escaping.

I'm not sure what the confusion is here: perhaps you were trying to enclose all the arguments to CONCAT with doublequotes? You are concatenating several strings, and each one must meet Excel's rules. Each string must then be delimited with a double-quote at each end. Thus, for any string whatsoever, you must write

"the_string" 

If your string contains a doublequote, that double quote has to be "escaped" by repeating it,

"the""string" 

So, in each instance of a string in your concatenation, follow that rule. Think of your first and last strings as

$DELIMITER""$DELIMITER

Because the value of $DELIMITER happens to be the doublequote, it looks ugly but does follow the rules.