How to create strings containing double quotes in Excel formulas?

Question

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

Maurice "The Rocket" Richard

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

Answer 1

Have you tried escaping with an additional double-quote? By escaping a character, you are telling Excel to treat the " character as literal text.

= "Maurice ""The Rocket"" Richard"

Answer 2

Alternatively, you can use the CHAR function:

= "Maurice " & CHAR(34) & "Rocket" & CHAR(34) & " Richard"

Answer 3

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

=CONCATENATE("""x"""," hi")  

= "x" hi

Answer 4

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

Function Quote(inputText As String) As String
  Quote = Chr(34) & inputText & Chr(34)
End Function

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

="Maurice "&Quote("Rocket")&" Richard"

This is similar to what Dave DuPlantis posted.

Answer 5

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

=CONCATENATE("'{""service"": { ""field"": "&A2&"}}'")

Answer 6

Use chr(34) Code:

    Joe = "Hi there, " & Chr(34) & "Joe" & Chr(34)
    ActiveCell.Value = Joe

Result:

    Hi there, "joe"

Answer 7

Concatenate " as a ceparate cell:

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

D1 displays "text"

Answer 8

will this work for macros using .Formula = "=THEFORMULAFUNCTION("STUFF")" so it would be like: will this work for macros using .Formula = "=THEFORMULAFUNCTION(CHAR(34) & STUFF & CHAR(34))"

Answer 9

Returning an empty or zero-length string (e.g. "") to make a cell appear blank is a common practise in a worksheet formula but recreating that option when inserting the formula through the Range.Formula or Range.FormulaR1C1 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 TEXT function can produce the same result without using quotes.

'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

To my eye, using TEXT(,) in place of "" 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 VLOOKUP to avoid returning a zero to the cell when a lookup results in a blank or returning an empty string on no-match with IFERROR.

'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(,))"

With TEXT(,) replacing the old "" 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.

Answer 10

="Maurice "&"""TheRocker"""&" Richard"

Answer 11

VBA Function

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

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

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

 THEFORMULAFUNCTION "STUFF"

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

 THEFORMULAFUNCTION "STUFF"

Excel Result/Formula

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

2) THEFORMULAFUNCTION "STUFF"

Answer 12

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:

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.