Excel adds quotation marks to the end of my formula
Clash Royale CLAN TAG#URR8PPP
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
microsoft-excel microsoft-excel-2016 special-characters concatenation escape-characters
edited Jan 31 at 16:04
fixer1234
18.8k144982
asked Jan 31 at 8:43
GabrielGabriel
113
add a comment |
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
microsoft-excel microsoft-excel-2016 special-characters concatenation escape-characters
edited Jan 31 at 16:04
fixer1234
18.8k144982
asked Jan 31 at 8:43
GabrielGabriel
113
2
Hey Gabriel, welcome to Superuser. You already posted an out to your problem yourself. Instead of using """. You can just use: ,CHAR(34) which gives a double quotation mark. You will have to use more commas, but this is the easiest solution I see.
– Michthan
Jan 31 at 10:48
@Michthan or just follow the Excel delimiter rules -- see my answer.
– Carl Witthoft
Jan 31 at 15:40
1
Excel is correcting your incorrect formula. You should be happy about this."""is not a valid string, while""""is ok
– edc65
Jan 31 at 16:27
Based on the answers from hannu, Michthan and fixer1234 I think I will use this formula =CONCAT(CHAR(34),A4,""": """,D4,CHAR(34),",") as it's the easiest to explain to the rest of the team involved in this project, which can be anyone from translators, to programmers and PM/admin staff. But feel free to comment, thanks.
– Gabriel
Feb 2 at 7:32
add a comment |
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
microsoft-excel microsoft-excel-2016 special-characters concatenation escape-characters
edited Jan 31 at 16:04
fixer1234
18.8k144982
asked Jan 31 at 8:43
GabrielGabriel
113
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
microsoft-excel microsoft-excel-2016 special-characters concatenation escape-characters
microsoft-excel microsoft-excel-2016 special-characters concatenation escape-characters
edited Jan 31 at 16:04
fixer1234
18.8k144982
asked Jan 31 at 8:43
GabrielGabriel
113
edited Jan 31 at 16:04
fixer1234
18.8k144982
asked Jan 31 at 8:43
GabrielGabriel
113
edited Jan 31 at 16:04
fixer1234
18.8k144982
edited Jan 31 at 16:04
fixer1234
18.8k144982
edited Jan 31 at 16:04
fixer1234
18.8k144982
18.8k144982
asked Jan 31 at 8:43
GabrielGabriel
113
asked Jan 31 at 8:43
GabrielGabriel
113
asked Jan 31 at 8:43
GabrielGabriel
113
113
2
Hey Gabriel, welcome to Superuser. You already posted an out to your problem yourself. Instead of using """. You can just use: ,CHAR(34) which gives a double quotation mark. You will have to use more commas, but this is the easiest solution I see.
– Michthan
Jan 31 at 10:48
@Michthan or just follow the Excel delimiter rules -- see my answer.
– Carl Witthoft
Jan 31 at 15:40
1
Excel is correcting your incorrect formula. You should be happy about this."""is not a valid string, while""""is ok
– edc65
Jan 31 at 16:27
Based on the answers from hannu, Michthan and fixer1234 I think I will use this formula =CONCAT(CHAR(34),A4,""": """,D4,CHAR(34),",") as it's the easiest to explain to the rest of the team involved in this project, which can be anyone from translators, to programmers and PM/admin staff. But feel free to comment, thanks.
– Gabriel
Feb 2 at 7:32
add a comment |
2
Hey Gabriel, welcome to Superuser. You already posted an out to your problem yourself. Instead of using """. You can just use: ,CHAR(34) which gives a double quotation mark. You will have to use more commas, but this is the easiest solution I see.
– Michthan
Jan 31 at 10:48
@Michthan or just follow the Excel delimiter rules -- see my answer.
– Carl Witthoft
Jan 31 at 15:40
1
Excel is correcting your incorrect formula. You should be happy about this."""is not a valid string, while""""is ok
– edc65
Jan 31 at 16:27
Based on the answers from hannu, Michthan and fixer1234 I think I will use this formula =CONCAT(CHAR(34),A4,""": """,D4,CHAR(34),",") as it's the easiest to explain to the rest of the team involved in this project, which can be anyone from translators, to programmers and PM/admin staff. But feel free to comment, thanks.
– Gabriel
Feb 2 at 7:32
2
2
Hey Gabriel, welcome to Superuser. You already posted an out to your problem yourself. Instead of using """. You can just use: ,CHAR(34) which gives a double quotation mark. You will have to use more commas, but this is the easiest solution I see.
– Michthan
Jan 31 at 10:48
Hey Gabriel, welcome to Superuser. You already posted an out to your problem yourself. Instead of using """. You can just use: ,CHAR(34) which gives a double quotation mark. You will have to use more commas, but this is the easiest solution I see.
– Michthan
Jan 31 at 10:48
@Michthan or just follow the Excel delimiter rules -- see my answer.
– Carl Witthoft
Jan 31 at 15:40
@Michthan or just follow the Excel delimiter rules -- see my answer.
– Carl Witthoft
Jan 31 at 15:40
1
1
Excel is correcting your incorrect formula. You should be happy about this.
""" is not a valid string, while """" is ok– edc65
Jan 31 at 16:27
Excel is correcting your incorrect formula. You should be happy about this.
""" is not a valid string, while """" is ok– edc65
Jan 31 at 16:27
Based on the answers from hannu, Michthan and fixer1234 I think I will use this formula =CONCAT(CHAR(34),A4,""": """,D4,CHAR(34),",") as it's the easiest to explain to the rest of the team involved in this project, which can be anyone from translators, to programmers and PM/admin staff. But feel free to comment, thanks.
– Gabriel
Feb 2 at 7:32
Based on the answers from hannu, Michthan and fixer1234 I think I will use this formula =CONCAT(CHAR(34),A4,""": """,D4,CHAR(34),",") as it's the easiest to explain to the rest of the team involved in this project, which can be anyone from translators, to programmers and PM/admin staff. But feel free to comment, thanks.
– Gabriel
Feb 2 at 7:32
add a comment |
3 Answers
3
active
oldest
votes
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).
answered Jan 31 at 12:48
fixer1234fixer1234
18.8k144982
I prefer Heath Bar Crunch myself
– Carl Witthoft
Jan 31 at 15:41
Shouldn't be hard to not get cross-eyed - the two easiest ways to tell whether it's a closing quote or a literal quote are 1. Read the opening quote. Then, while the next quotes come in pairs, they are literal quotes. If not, it's a closing quote. Alternatively, you can split them into essentially smaller strings -""":"""becomes""":""". Then, just join all the strings together with quotes
– somebody
Feb 1 at 1:41
My complete formula is actually =CONCAT(CHAR(34),A4,""": """,D4,"""",",") as I'm creating a JSON string in the G column from data in column A and column D. So would you recomend this formula for this? =""""&A4&""":"""&D4&"""," I want the result to look like this: "welcome": "Bienvenue!",
– Gabriel
Feb 1 at 14:22
@Gabriel, did you try it to see what you get? :-) That looks right, although in your desired output in your comment it looks like there's a space after the colon, which you would need to include if that's right: . . . (""": """vs.""":"""). It's personal preference whether to use CONCAT with commas vs. ampersands; same with""""vs. CHAR(34) for a single concatenated quote. So the version of the formula in your comment on Hannu's answer will also work (same point with the apparent space after the colon:": "vs.":").
– fixer1234
Feb 1 at 16:57
@fixer1234 Oh, I saw now that I didn't get the space, thanks! :-) . Yes I need the space after comma. I'm just trying to find a formula which anyone envolved with the Excel file easily understands, and easily can change, so I just want to follow some standard Excel praxis.
– Gabriel
Feb 2 at 7:11
add a comment |
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.
answered Jan 31 at 11:50
HannuHannu
4,1451925
Nothing wrong with 4 doublequotes in a row other than it looks ugly
– Carl Witthoft
Jan 31 at 15:40
1
@CarlWitthoft, unless you need to diagnose a typing error in a long formula full of triple and quadruple double quotes. That's one of two things that can make you go blind. :-)
– fixer1234
Jan 31 at 15:47
@fixer1234 True that -- or 7-deep parentheses
– Carl Witthoft
Jan 31 at 18:11
My complete formula is actually =CONCAT(CHAR(34),A4,""": """,D4,"""",",") as I'm creating a JSON string in the G column from data in column A and column D. So would you recomend this formula for this? =CONCAT(CHAR(34),A4,CHAR(34),":",CHAR(34),D4,CHAR(34),",",) I want the result to look like this: "welcome": "Bienvenue!",
– Gabriel
Feb 1 at 14:05
Really, the choice is yours, I have hard to believe the choices makes much difference speed-wise, at least not when using moderate amounts of data. There might be a difference if you have huge data amounts though. I'd go for good readbility - so that any later review (after a year or so maybe) would be easier; e.g. if attempting changes.
– Hannu
Feb 1 at 20:40
|
show 2 more comments
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.
answered Jan 31 at 15:39
Carl WitthoftCarl Witthoft
362314
Carl great answer, this makes perfect sense to me!
– Michthan
Feb 1 at 14:51
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "3"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1400438%2fexcel-adds-quotation-marks-to-the-end-of-my-formula%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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).
answered Jan 31 at 12:48
fixer1234fixer1234
18.8k144982
I prefer Heath Bar Crunch myself
– Carl Witthoft
Jan 31 at 15:41
Shouldn't be hard to not get cross-eyed - the two easiest ways to tell whether it's a closing quote or a literal quote are 1. Read the opening quote. Then, while the next quotes come in pairs, they are literal quotes. If not, it's a closing quote. Alternatively, you can split them into essentially smaller strings -""":"""becomes""":""". Then, just join all the strings together with quotes
– somebody
Feb 1 at 1:41
My complete formula is actually =CONCAT(CHAR(34),A4,""": """,D4,"""",",") as I'm creating a JSON string in the G column from data in column A and column D. So would you recomend this formula for this? =""""&A4&""":"""&D4&"""," I want the result to look like this: "welcome": "Bienvenue!",
– Gabriel
Feb 1 at 14:22
@Gabriel, did you try it to see what you get? :-) That looks right, although in your desired output in your comment it looks like there's a space after the colon, which you would need to include if that's right: . . . (""": """vs.""":"""). It's personal preference whether to use CONCAT with commas vs. ampersands; same with""""vs. CHAR(34) for a single concatenated quote. So the version of the formula in your comment on Hannu's answer will also work (same point with the apparent space after the colon:": "vs.":").
– fixer1234
Feb 1 at 16:57
@fixer1234 Oh, I saw now that I didn't get the space, thanks! :-) . Yes I need the space after comma. I'm just trying to find a formula which anyone envolved with the Excel file easily understands, and easily can change, so I just want to follow some standard Excel praxis.
– Gabriel
Feb 2 at 7:11
add a comment |
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).
answered Jan 31 at 12:48
fixer1234fixer1234
18.8k144982
I prefer Heath Bar Crunch myself
– Carl Witthoft
Jan 31 at 15:41
Shouldn't be hard to not get cross-eyed - the two easiest ways to tell whether it's a closing quote or a literal quote are 1. Read the opening quote. Then, while the next quotes come in pairs, they are literal quotes. If not, it's a closing quote. Alternatively, you can split them into essentially smaller strings -""":"""becomes""":""". Then, just join all the strings together with quotes
– somebody
Feb 1 at 1:41
My complete formula is actually =CONCAT(CHAR(34),A4,""": """,D4,"""",",") as I'm creating a JSON string in the G column from data in column A and column D. So would you recomend this formula for this? =""""&A4&""":"""&D4&"""," I want the result to look like this: "welcome": "Bienvenue!",
– Gabriel
Feb 1 at 14:22
@Gabriel, did you try it to see what you get? :-) That looks right, although in your desired output in your comment it looks like there's a space after the colon, which you would need to include if that's right: . . . (""": """vs.""":"""). It's personal preference whether to use CONCAT with commas vs. ampersands; same with""""vs. CHAR(34) for a single concatenated quote. So the version of the formula in your comment on Hannu's answer will also work (same point with the apparent space after the colon:": "vs.":").
– fixer1234
Feb 1 at 16:57
@fixer1234 Oh, I saw now that I didn't get the space, thanks! :-) . Yes I need the space after comma. I'm just trying to find a formula which anyone envolved with the Excel file easily understands, and easily can change, so I just want to follow some standard Excel praxis.
– Gabriel
Feb 2 at 7:11
add a comment |
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).
answered Jan 31 at 12:48
fixer1234fixer1234
18.8k144982
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).
answered Jan 31 at 12:48
fixer1234fixer1234
18.8k144982
edited Jan 31 at 16:41
answered Jan 31 at 12:48
fixer1234fixer1234
18.8k144982
answered Jan 31 at 12:48
fixer1234fixer1234
18.8k144982
answered Jan 31 at 12:48
fixer1234fixer1234
18.8k144982
18.8k144982
I prefer Heath Bar Crunch myself
– Carl Witthoft
Jan 31 at 15:41
Shouldn't be hard to not get cross-eyed - the two easiest ways to tell whether it's a closing quote or a literal quote are 1. Read the opening quote. Then, while the next quotes come in pairs, they are literal quotes. If not, it's a closing quote. Alternatively, you can split them into essentially smaller strings -""":"""becomes""":""". Then, just join all the strings together with quotes
– somebody
Feb 1 at 1:41
My complete formula is actually =CONCAT(CHAR(34),A4,""": """,D4,"""",",") as I'm creating a JSON string in the G column from data in column A and column D. So would you recomend this formula for this? =""""&A4&""":"""&D4&"""," I want the result to look like this: "welcome": "Bienvenue!",
– Gabriel
Feb 1 at 14:22
@Gabriel, did you try it to see what you get? :-) That looks right, although in your desired output in your comment it looks like there's a space after the colon, which you would need to include if that's right: . . . (""": """vs.""":"""). It's personal preference whether to use CONCAT with commas vs. ampersands; same with""""vs. CHAR(34) for a single concatenated quote. So the version of the formula in your comment on Hannu's answer will also work (same point with the apparent space after the colon:": "vs.":").
– fixer1234
Feb 1 at 16:57
@fixer1234 Oh, I saw now that I didn't get the space, thanks! :-) . Yes I need the space after comma. I'm just trying to find a formula which anyone envolved with the Excel file easily understands, and easily can change, so I just want to follow some standard Excel praxis.
– Gabriel
Feb 2 at 7:11
add a comment |
I prefer Heath Bar Crunch myself
– Carl Witthoft
Jan 31 at 15:41
Shouldn't be hard to not get cross-eyed - the two easiest ways to tell whether it's a closing quote or a literal quote are 1. Read the opening quote. Then, while the next quotes come in pairs, they are literal quotes. If not, it's a closing quote. Alternatively, you can split them into essentially smaller strings -""":"""becomes""":""". Then, just join all the strings together with quotes
– somebody
Feb 1 at 1:41
My complete formula is actually =CONCAT(CHAR(34),A4,""": """,D4,"""",",") as I'm creating a JSON string in the G column from data in column A and column D. So would you recomend this formula for this? =""""&A4&""":"""&D4&"""," I want the result to look like this: "welcome": "Bienvenue!",
– Gabriel
Feb 1 at 14:22
@Gabriel, did you try it to see what you get? :-) That looks right, although in your desired output in your comment it looks like there's a space after the colon, which you would need to include if that's right: . . . (""": """vs.""":"""). It's personal preference whether to use CONCAT with commas vs. ampersands; same with""""vs. CHAR(34) for a single concatenated quote. So the version of the formula in your comment on Hannu's answer will also work (same point with the apparent space after the colon:": "vs.":").
– fixer1234
Feb 1 at 16:57
@fixer1234 Oh, I saw now that I didn't get the space, thanks! :-) . Yes I need the space after comma. I'm just trying to find a formula which anyone envolved with the Excel file easily understands, and easily can change, so I just want to follow some standard Excel praxis.
– Gabriel
Feb 2 at 7:11
I prefer Heath Bar Crunch myself
– Carl Witthoft
Jan 31 at 15:41
I prefer Heath Bar Crunch myself
– Carl Witthoft
Jan 31 at 15:41
Shouldn't be hard to not get cross-eyed - the two easiest ways to tell whether it's a closing quote or a literal quote are 1. Read the opening quote. Then, while the next quotes come in pairs, they are literal quotes. If not, it's a closing quote. Alternatively, you can split them into essentially smaller strings -
""":""" becomes "" ":" "". Then, just join all the strings together with quotes– somebody
Feb 1 at 1:41
Shouldn't be hard to not get cross-eyed - the two easiest ways to tell whether it's a closing quote or a literal quote are 1. Read the opening quote. Then, while the next quotes come in pairs, they are literal quotes. If not, it's a closing quote. Alternatively, you can split them into essentially smaller strings -
""":""" becomes "" ":" "". Then, just join all the strings together with quotes– somebody
Feb 1 at 1:41
My complete formula is actually =CONCAT(CHAR(34),A4,""": """,D4,"""",",") as I'm creating a JSON string in the G column from data in column A and column D. So would you recomend this formula for this? =""""&A4&""":"""&D4&"""," I want the result to look like this: "welcome": "Bienvenue!",
– Gabriel
Feb 1 at 14:22
My complete formula is actually =CONCAT(CHAR(34),A4,""": """,D4,"""",",") as I'm creating a JSON string in the G column from data in column A and column D. So would you recomend this formula for this? =""""&A4&""":"""&D4&"""," I want the result to look like this: "welcome": "Bienvenue!",
– Gabriel
Feb 1 at 14:22
@Gabriel, did you try it to see what you get? :-) That looks right, although in your desired output in your comment it looks like there's a space after the colon, which you would need to include if that's right: . . . (
""": """ vs. """:"""). It's personal preference whether to use CONCAT with commas vs. ampersands; same with """" vs. CHAR(34) for a single concatenated quote. So the version of the formula in your comment on Hannu's answer will also work (same point with the apparent space after the colon: ": " vs. ":").– fixer1234
Feb 1 at 16:57
@Gabriel, did you try it to see what you get? :-) That looks right, although in your desired output in your comment it looks like there's a space after the colon, which you would need to include if that's right: . . . (
""": """ vs. """:"""). It's personal preference whether to use CONCAT with commas vs. ampersands; same with """" vs. CHAR(34) for a single concatenated quote. So the version of the formula in your comment on Hannu's answer will also work (same point with the apparent space after the colon: ": " vs. ":").– fixer1234
Feb 1 at 16:57
@fixer1234 Oh, I saw now that I didn't get the space, thanks! :-) . Yes I need the space after comma. I'm just trying to find a formula which anyone envolved with the Excel file easily understands, and easily can change, so I just want to follow some standard Excel praxis.
– Gabriel
Feb 2 at 7:11
@fixer1234 Oh, I saw now that I didn't get the space, thanks! :-) . Yes I need the space after comma. I'm just trying to find a formula which anyone envolved with the Excel file easily understands, and easily can change, so I just want to follow some standard Excel praxis.
– Gabriel
Feb 2 at 7:11
add a comment |
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.
answered Jan 31 at 11:50
HannuHannu
4,1451925
Nothing wrong with 4 doublequotes in a row other than it looks ugly
– Carl Witthoft
Jan 31 at 15:40
1
@CarlWitthoft, unless you need to diagnose a typing error in a long formula full of triple and quadruple double quotes. That's one of two things that can make you go blind. :-)
– fixer1234
Jan 31 at 15:47
@fixer1234 True that -- or 7-deep parentheses
– Carl Witthoft
Jan 31 at 18:11
My complete formula is actually =CONCAT(CHAR(34),A4,""": """,D4,"""",",") as I'm creating a JSON string in the G column from data in column A and column D. So would you recomend this formula for this? =CONCAT(CHAR(34),A4,CHAR(34),":",CHAR(34),D4,CHAR(34),",",) I want the result to look like this: "welcome": "Bienvenue!",
– Gabriel
Feb 1 at 14:05
Really, the choice is yours, I have hard to believe the choices makes much difference speed-wise, at least not when using moderate amounts of data. There might be a difference if you have huge data amounts though. I'd go for good readbility - so that any later review (after a year or so maybe) would be easier; e.g. if attempting changes.
– Hannu
Feb 1 at 20:40
|
show 2 more comments
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.
answered Jan 31 at 11:50
HannuHannu
4,1451925
Nothing wrong with 4 doublequotes in a row other than it looks ugly
– Carl Witthoft
Jan 31 at 15:40
1
@CarlWitthoft, unless you need to diagnose a typing error in a long formula full of triple and quadruple double quotes. That's one of two things that can make you go blind. :-)
– fixer1234
Jan 31 at 15:47
@fixer1234 True that -- or 7-deep parentheses
– Carl Witthoft
Jan 31 at 18:11
My complete formula is actually =CONCAT(CHAR(34),A4,""": """,D4,"""",",") as I'm creating a JSON string in the G column from data in column A and column D. So would you recomend this formula for this? =CONCAT(CHAR(34),A4,CHAR(34),":",CHAR(34),D4,CHAR(34),",",) I want the result to look like this: "welcome": "Bienvenue!",
– Gabriel
Feb 1 at 14:05
Really, the choice is yours, I have hard to believe the choices makes much difference speed-wise, at least not when using moderate amounts of data. There might be a difference if you have huge data amounts though. I'd go for good readbility - so that any later review (after a year or so maybe) would be easier; e.g. if attempting changes.
– Hannu
Feb 1 at 20:40
|
show 2 more comments
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.
answered Jan 31 at 11:50
HannuHannu
4,1451925
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.
answered Jan 31 at 11:50
HannuHannu
4,1451925
edited Jan 31 at 16:56
answered Jan 31 at 11:50
HannuHannu
4,1451925
answered Jan 31 at 11:50
HannuHannu
4,1451925
answered Jan 31 at 11:50
HannuHannu
4,1451925
4,1451925
Nothing wrong with 4 doublequotes in a row other than it looks ugly
– Carl Witthoft
Jan 31 at 15:40
1
@CarlWitthoft, unless you need to diagnose a typing error in a long formula full of triple and quadruple double quotes. That's one of two things that can make you go blind. :-)
– fixer1234
Jan 31 at 15:47
@fixer1234 True that -- or 7-deep parentheses
– Carl Witthoft
Jan 31 at 18:11
My complete formula is actually =CONCAT(CHAR(34),A4,""": """,D4,"""",",") as I'm creating a JSON string in the G column from data in column A and column D. So would you recomend this formula for this? =CONCAT(CHAR(34),A4,CHAR(34),":",CHAR(34),D4,CHAR(34),",",) I want the result to look like this: "welcome": "Bienvenue!",
– Gabriel
Feb 1 at 14:05
Really, the choice is yours, I have hard to believe the choices makes much difference speed-wise, at least not when using moderate amounts of data. There might be a difference if you have huge data amounts though. I'd go for good readbility - so that any later review (after a year or so maybe) would be easier; e.g. if attempting changes.
– Hannu
Feb 1 at 20:40
|
show 2 more comments
Nothing wrong with 4 doublequotes in a row other than it looks ugly
– Carl Witthoft
Jan 31 at 15:40
1
@CarlWitthoft, unless you need to diagnose a typing error in a long formula full of triple and quadruple double quotes. That's one of two things that can make you go blind. :-)
– fixer1234
Jan 31 at 15:47
@fixer1234 True that -- or 7-deep parentheses
– Carl Witthoft
Jan 31 at 18:11
My complete formula is actually =CONCAT(CHAR(34),A4,""": """,D4,"""",",") as I'm creating a JSON string in the G column from data in column A and column D. So would you recomend this formula for this? =CONCAT(CHAR(34),A4,CHAR(34),":",CHAR(34),D4,CHAR(34),",",) I want the result to look like this: "welcome": "Bienvenue!",
– Gabriel
Feb 1 at 14:05
Really, the choice is yours, I have hard to believe the choices makes much difference speed-wise, at least not when using moderate amounts of data. There might be a difference if you have huge data amounts though. I'd go for good readbility - so that any later review (after a year or so maybe) would be easier; e.g. if attempting changes.
– Hannu
Feb 1 at 20:40
Nothing wrong with 4 doublequotes in a row other than it looks ugly
– Carl Witthoft
Jan 31 at 15:40
Nothing wrong with 4 doublequotes in a row other than it looks ugly
– Carl Witthoft
Jan 31 at 15:40
1
1
@CarlWitthoft, unless you need to diagnose a typing error in a long formula full of triple and quadruple double quotes. That's one of two things that can make you go blind. :-)
– fixer1234
Jan 31 at 15:47
@CarlWitthoft, unless you need to diagnose a typing error in a long formula full of triple and quadruple double quotes. That's one of two things that can make you go blind. :-)
– fixer1234
Jan 31 at 15:47
@fixer1234 True that -- or 7-deep parentheses
– Carl Witthoft
Jan 31 at 18:11
@fixer1234 True that -- or 7-deep parentheses
– Carl Witthoft
Jan 31 at 18:11
My complete formula is actually =CONCAT(CHAR(34),A4,""": """,D4,"""",",") as I'm creating a JSON string in the G column from data in column A and column D. So would you recomend this formula for this? =CONCAT(CHAR(34),A4,CHAR(34),":",CHAR(34),D4,CHAR(34),",",) I want the result to look like this: "welcome": "Bienvenue!",
– Gabriel
Feb 1 at 14:05
My complete formula is actually =CONCAT(CHAR(34),A4,""": """,D4,"""",",") as I'm creating a JSON string in the G column from data in column A and column D. So would you recomend this formula for this? =CONCAT(CHAR(34),A4,CHAR(34),":",CHAR(34),D4,CHAR(34),",",) I want the result to look like this: "welcome": "Bienvenue!",
– Gabriel
Feb 1 at 14:05
Really, the choice is yours, I have hard to believe the choices makes much difference speed-wise, at least not when using moderate amounts of data. There might be a difference if you have huge data amounts though. I'd go for good readbility - so that any later review (after a year or so maybe) would be easier; e.g. if attempting changes.
– Hannu
Feb 1 at 20:40
Really, the choice is yours, I have hard to believe the choices makes much difference speed-wise, at least not when using moderate amounts of data. There might be a difference if you have huge data amounts though. I'd go for good readbility - so that any later review (after a year or so maybe) would be easier; e.g. if attempting changes.
– Hannu
Feb 1 at 20:40
|
show 2 more comments
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.
answered Jan 31 at 15:39
Carl WitthoftCarl Witthoft
362314
Carl great answer, this makes perfect sense to me!
– Michthan
Feb 1 at 14:51
add a comment |
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.
answered Jan 31 at 15:39
Carl WitthoftCarl Witthoft
362314
Carl great answer, this makes perfect sense to me!
– Michthan
Feb 1 at 14:51
add a comment |
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.
answered Jan 31 at 15:39
Carl WitthoftCarl Witthoft
362314
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.
answered Jan 31 at 15:39
Carl WitthoftCarl Witthoft
362314
answered Jan 31 at 15:39
Carl WitthoftCarl Witthoft
362314
answered Jan 31 at 15:39
Carl WitthoftCarl Witthoft
362314
answered Jan 31 at 15:39
Carl WitthoftCarl Witthoft
362314
362314
Carl great answer, this makes perfect sense to me!
– Michthan
Feb 1 at 14:51
add a comment |
Carl great answer, this makes perfect sense to me!
– Michthan
Feb 1 at 14:51
Carl great answer, this makes perfect sense to me!
– Michthan
Feb 1 at 14:51
Carl great answer, this makes perfect sense to me!
– Michthan
Feb 1 at 14:51
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1400438%2fexcel-adds-quotation-marks-to-the-end-of-my-formula%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Hey Gabriel, welcome to Superuser. You already posted an out to your problem yourself. Instead of using """. You can just use: ,CHAR(34) which gives a double quotation mark. You will have to use more commas, but this is the easiest solution I see.
– Michthan
Jan 31 at 10:48
@Michthan or just follow the Excel delimiter rules -- see my answer.
– Carl Witthoft
Jan 31 at 15:40
1
Excel is correcting your incorrect formula. You should be happy about this.
"""is not a valid string, while""""is ok– edc65
Jan 31 at 16:27
Based on the answers from hannu, Michthan and fixer1234 I think I will use this formula =CONCAT(CHAR(34),A4,""": """,D4,CHAR(34),",") as it's the easiest to explain to the rest of the team involved in this project, which can be anyone from translators, to programmers and PM/admin staff. But feel free to comment, thanks.
– Gabriel
Feb 2 at 7:32