blocks|key|3360579|text|您是否尝试过使用额外的双引号进行转义？通过对字符进行转义，您告诉Excel将“字符视为文字文本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3360580|=+"Maurice+""The+Rocket""+Richard"|code-block|syntax|javascript|3360581|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Have you tried escaping with an additional double-quote? By escaping a character, you are telling Excel to treat the &quot; character as literal text.
<pre class="lang-sql prettyprint-override"><code>= &quot;Maurice &quot;&quot;The Rocket&quot;&quot; Richard&quot;
</code></pre>

blocks|key|360119|text|或者，您可以使用CHAR函数：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|360120|=+"Maurice+"+&+CHAR(34)+&+"Rocket"+&+CHAR(34)+&+"+Richard"|code-block|syntax|javascript|360121|entityMap^0|8|4|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Alternatively, you can use the <code>CHAR</code> function:

<pre><code>= "Maurice " &amp; CHAR(34) &amp; "Rocket" &amp; CHAR(34) &amp; " Richard"
</code></pre>

blocks|key|3147913|text|三个双引号："+"+"+x+"+"+"+=+"x"+Excel将自动变为一个双引号。例如：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3147914|=CONCATENATE("""x""","+hi")++|code-block|syntax|javascript|3147915|=+"x“高|3147916|entityMap^0|6|D|M|3|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

Three double quotes: <code>" " " x " " "</code> = <code>"x"</code> Excel will auto change to one double quote. e.g.: 

<pre><code>=CONCATENATE("""x"""," hi") 
</code></pre>

= "x" hi

blocks|key|3147893|text|为此，我使用了一个函数(如果工作簿已经有了VBA)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3147894|Function+Quote(inputText+As+String)+As+String
++Quote+=+Chr(34)+&+inputText+&+Chr(34)
End+Function|code-block|syntax|javascript|3147895|这来自Sue+Mosher的书"Microsoft+Outlook+Programming“。那么你的公式就是：|3147896|="Maurice+"&Quote("Rocket")&"+Richard"|offset|length|style|CODE|3147897|这与Dave+DuPlantis发布的内容类似。|3147898|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/218474/190829^0|0|0|0|0|12|0|2|E|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|11|8|@$K|12|L|13|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|14|8|@]|9|@$K|15|L|16|1|17]]|A|$]]|$1|Q|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

I use a function for this (if the workbook already has VBA).

<pre><code>Function Quote(inputText As String) As String
 Quote = Chr(34) &amp; inputText &amp; Chr(34)
End Function
</code></pre>

This is from Sue Mosher's book "Microsoft Outlook Programming". Then your formula would be:

<code>="Maurice "&amp;Quote("Rocket")&amp;" Richard"</code>

This is similar to what <a href="https://stackoverflow.com/a/218474/190829">Dave DuPlantis</a> posted.

blocks|key|3360721|text|如果您需要使用JSON执行此操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3360722|=CONCATENATE("'{""service"":+{+""field"":+"&A2&"}}'")|code-block|syntax|javascript|3360723|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

In the event that you need to do this with JSON:

<pre><code>=CONCATENATE("'{""service"": { ""field"": "&amp;A2&amp;"}}'")
</code></pre>

blocks|key|362787|text|使用chr(34)代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|362788|++++Joe+=+"Hi+there,+"+&+Chr(34)+&+"Joe"+&+Chr(34)
++++ActiveCell.Value+=+Joe|code-block|syntax|javascript|362789|结果：|362790|++++Hi+there,+"joe"|362791|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Use chr(34)
Code:

<pre><code> Joe = "Hi there, " &amp; Chr(34) &amp; "Joe" &amp; Chr(34)
 ActiveCell.Value = Joe
</code></pre>

Result:

<pre><code> Hi there, "joe"
</code></pre>

blocks|key|3360852|text|将"连接为单独的单元格：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3360853|++++A+%7C+++B+++%7C+C+%7C+D
1+++"+%7C+text++%7C+"+%7C+=CONCATENATE(A1;+B1;+C1);

D1+displays+"text"|code-block|syntax|javascript|3360854|entityMap^0|1|1|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Concatenate <code>"</code> as a ceparate cell:

<pre><code> A | B | C | D
1 " | text | " | =CONCATENATE(A1; B1; C1);

D1 displays "text"
</code></pre>

blocks|key|360125|text|这是否适用于使用.Formula+=+"=THEFORMULAFUNCTION("STUFF")"的宏，如下所示:这是否适用于使用.Formula+=+"=THEFORMULAFUNCTION(CHAR(34)+&+STUFF+&+CHAR(34))"的宏|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|360126|entityMap^0|8|15|1T|1P|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

will this work for macros using <code>.Formula = "=THEFORMULAFUNCTION("STUFF")"</code>
so it would be like:
will this work for macros using <code>.Formula = "=THEFORMULAFUNCTION(CHAR(34) &amp; STUFF &amp; CHAR(34))"</code>

blocks|key|360230|text|返回空字符串或零长度字符串(例如"")以使单元格显示为空是工作表公式中的一种常见做法，但是在VBA中通过Range.Formula或Range.FormulaR1C1属性插入公式时重新创建该选项是很麻烦的，因为必须将双引号字符放在带引号的字符串中。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|360231|工作表的原生TEXT+function可以在不使用引号的情况下产生相同的结果。|360232|'formula+to+insert+into+C1+-+=IF(A1<>"",+B1,+"")
range("C1").formula+=+"=IF(A1<>"""",+B1,+"""")"+++++++++'<~quote+chars+doubled+up
range("C1").formula+=+"=IF(A1<>TEXT(,),+B1,+TEXT(,))"+++'<~with+TEXT(,)+instead|code-block|syntax|javascript|360233|在我看来，使用TEXT(,)代替""甚至可以清理上面这样一个简单的公式。在更复杂的公式中使用时，这种好处变得越来越明显，比如在查找结果为空时将空字符串附加到VLOOKUP以避免向单元格返回零，或者在使用IFERROR时不匹配时返回空字符串。|360234|'formula+to+insert+into+D1+-+=IFERROR(VLOOKUP(A1,+B:C,+2,+FALSE)&"",+"")
range("D1").formula+=+"=IFERROR(VLOOKUP(A1,+B:C,+2,+FALSE)&"""",+"""")"
range("D1").formula+=+"=IFERROR(VLOOKUP(A1,+B:C,+2,+FALSE)&TEXT(,),+TEXT(,))"|360235|随着TEXT(,)取代传递空字符串的旧""方法，您可能会停止使用算盘来确定公式字符串中是否有正确数量的引号字符。|360236|entityMap|0|LINK|mutability|MUTABLE|url|https://msdn.microsoft.com/en-us/library/office/ff838835.aspx|1|https://msdn.microsoft.com/en-us/library/bb213527.aspx|2|https://support.office.com/en-us/article/TEXT-function-4B46E545-E612-4C84-AC23-EDFA68007945|3|https://support.office.com/en-us/article/vlookup-function-adceda66-30de-4f26-923b-7257939faa65|4|https://support.office.com/en-us/article/IFERROR-function-F59BACDC-78BD-4924-91DF-A869D0B08CD5^0|G|2|1G|D|0|1U|H|1|0|6|D|2|0|0|7|7|G|2|26|7|3|2T|7|4|0|0|2|7|J|2|0^^$0|@$1|2|3|4|5|6|7|18|8|@$9|19|A|1A|B|C]]|D|@$9|1B|A|1C|1|1D]|$9|1E|A|1F|1|1G]]|E|$]]|$1|F|3|G|5|6|7|1H|8|@]|D|@$9|1I|A|1J|1|1K]]|E|$]]|$1|H|3|I|5|J|7|1L|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1M|8|@$9|1N|A|1O|B|C]|$9|1P|A|1Q|B|C]]|D|@$9|1R|A|1S|1|1T]|$9|1U|A|1V|1|1W]]|E|$]]|$1|O|3|P|5|J|7|1X|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|1Y|8|@$9|1Z|A|20|B|C]|$9|21|A|22|B|C]]|D|@]|E|$]]|$1|S|3|-4|5|6|7|23|8|@]|D|@]|E|$]]]|T|$U|$5|V|W|X|E|$Y|Z]]|10|$5|V|W|X|E|$Y|11]]|12|$5|V|W|X|E|$Y|13]]|14|$5|V|W|X|E|$Y|15]]|16|$5|V|W|X|E|$Y|17]]]]

Returning an empty or zero-length string (e.g. <code>""</code>) to make a cell appear blank is a common practise in a worksheet formula but recreating that option when inserting the formula through the <a href="https://msdn.microsoft.com/en-us/library/office/ff838835.aspx" rel="nofollow">Range.Formula</a> or <a href="https://msdn.microsoft.com/en-us/library/bb213527.aspx" rel="nofollow">Range.FormulaR1C1</a> property in VBA is unwieldy due to the necessity of having to double-up the double-quote characters within a quoted string.

The worksheet's native <a href="https://support.office.com/en-us/article/TEXT-function-4B46E545-E612-4C84-AC23-EDFA68007945" rel="nofollow">TEXT function</a> can produce the same result without using quotes.

<pre><code>'formula to insert into C1 - =IF(A1&lt;&gt;"", B1, "")
range("C1").formula = "=IF(A1&lt;&gt;"""", B1, """")" '&lt;~quote chars doubled up
range("C1").formula = "=IF(A1&lt;&gt;TEXT(,), B1, TEXT(,))" '&lt;~with TEXT(,) instead
</code></pre>

To my eye, using <code>TEXT(,)</code> in place of <code>""</code> cleans up even a simple formula like the one above. The benefits become increasingly significant when used in more complicated formulas like the practise of appending an empty string to a <a href="https://support.office.com/en-us/article/vlookup-function-adceda66-30de-4f26-923b-7257939faa65" rel="nofollow">VLOOKUP</a> to avoid returning a zero to the cell when a lookup results in a blank or returning an empty string on no-match with <a href="https://support.office.com/en-us/article/IFERROR-function-F59BACDC-78BD-4924-91DF-A869D0B08CD5" rel="nofollow">IFERROR</a>.

<pre><code>'formula to insert into D1 - =IFERROR(VLOOKUP(A1, B:C, 2, FALSE)&amp;"", "")
range("D1").formula = "=IFERROR(VLOOKUP(A1, B:C, 2, FALSE)&amp;"""", """")"
range("D1").formula = "=IFERROR(VLOOKUP(A1, B:C, 2, FALSE)&amp;TEXT(,), TEXT(,))"
</code></pre>

With <code>TEXT(,)</code> replacing the old <code>""</code> method of delivering an empty string, you might get to stop using an abacus to determine whether you have the right number of quote characters in a formula string.

blocks|key|358105|text|="Maurice+"&"""TheRocker"""&"+Richard"|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|358106|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>="Maurice "&amp;"""TheRocker"""&amp;" Richard"
</code></pre>

blocks|key|358044|text|VBA函数|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|358045|1)+.Formula+=+"=""THEFORMULAFUNCTION+""&(CHAR(34)+&+""STUFF"“&+CHAR(34))”|358046|2)+.Formula+=+"THEFORMULAFUNCTION“”STUFF“”|358047|第一种方法使用vba在单元格中写入一个公式，从而得到计算值：|358048|+THEFORMULAFUNCTION+"STUFF"|code-block|syntax|javascript|358049|第二种方法使用vba在单元格中写入一个字符串，该字符串的结果为：|358050|+THEFORMULAFUNCTION+"STUFF"|358051|Excel结果/公式|358052|1)+="THEFORMULAFUNCTION+"&(CHAR(34)+&+"STUFF“&+CHAR(34))|358053|2)“材料”|358054|entityMap^0|0|5|0|0|0|0|0|0|0|0|A|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@$9|13|A|14|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|15|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|16|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|17|8|@]|D|@]|E|$]]|$1|L|3|M|5|N|7|18|8|@]|D|@]|E|$O|P]]|$1|Q|3|R|5|6|7|19|8|@]|D|@]|E|$]]|$1|S|3|T|5|N|7|1A|8|@]|D|@]|E|$O|P]]|$1|U|3|V|5|6|7|1B|8|@$9|1C|A|1D|B|C]]|D|@]|E|$]]|$1|W|3|X|5|6|7|1E|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|6|7|1F|8|@]|D|@]|E|$]]|$1|10|3|-4|5|6|7|1G|8|@]|D|@]|E|$]]]|11|$]]

VBA Function

1) .Formula = "=""THEFORMULAFUNCTION ""&amp;(CHAR(34) &amp; ""STUFF"" &amp; CHAR(34))"

2) .Formula = "THEFORMULAFUNCTION ""STUFF"""

The first method uses vba to write a formula in a cell which results in the calculated value:

<pre><code> THEFORMULAFUNCTION "STUFF"
</code></pre>

The second method uses vba to write a string in a cell which results in the value:

<pre><code> THEFORMULAFUNCTION "STUFF"
</code></pre>

Excel Result/Formula

1) ="THEFORMULAFUNCTION "&amp;(CHAR(34) &amp; "STUFF" &amp; CHAR(34))

2) THEFORMULAFUNCTION "STUFF"

blocks|key|362843|text|还有另一种方法，尽管对于“我如何在Excel公式中构造以下字符串："Maurice+"The+Rocket”Richard“”比“如何在Excel公式中创建包含双引号的字符串？”有更多的方法，但更多的是使用两个单引号：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|362844|​|362845|📷|atomic|offset|length|362846|362847|左边是从Excel工作表中截取的Calibri，右边是从VBA窗口中截取的截图。在我看来，@YonahW提到的转义赢得了“不费吹灰之力”，但两个单引号并不比两个双引号打字多，而且这种差异在VBA中相当明显，没有额外的击键，而在电子表格中则可能不明显。|362848|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/a44e87ea7787c7bd23b65e5e514b707e.jpeg|imageAlt^0|0|0|0|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@$G|X|H|Y|1|Z]]|A|$]]|$1|I|3|C|5|6|7|10|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|11|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S|T|-4]]]]

There is another way, though more for " How can I construct the following string in an Excel formula: "Maurice "The Rocket" Richard" " than " How to create strings containing double quotes in Excel formulas? ", which is simply to use two single quotes:

<a href="https://i.stack.imgur.com/WCRke.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WCRke.jpg" alt="SO216616 example"></a>

On the left is Calibri snipped from an Excel worksheet and on the right a snip from a VBA window.
In my view escaping as mentioned by @YonahW wins 'hands down' but two single quotes is no more typing than two doubles and the difference is reasonably apparent in VBA without additional keystrokes while, potentially, not noticeable in a spreadsheet.

How can I construct the following string in an Excel formula:

<blockquote>
 Maurice "The Rocket" Richard
</blockquote>

If I'm using single quotes, it's trivial = <code>"Maurice 'The Rocket' Richard"</code> but what about double quotes?

How to create strings containing double quotes in Excel formulas?

JSON

如何在Excel公式中构造以下字符串：理查德·莫里斯“火箭”如果我使用单引号，它很简单= "Maurice 'The Rocket' Richard"，但是双引号呢？

问如何在Excel公式中创建包含双引号的字符串？
EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Excel公式中创建包含双引号的字符串？EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Excel公式中创建包含双引号的字符串？
EN