[excel] How to create strings containing double quotes in Excel formulas?

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?

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.

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"

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.

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))"

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.

Concatenate " as a ceparate cell:

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

D1 displays "text"

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

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.

Alternatively, you can use the CHAR function:

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

Concatenate " as a ceparate cell:

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

D1 displays "text"

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

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

= "x" hi

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.

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.

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

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

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"

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

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

= "x" hi

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

Alternatively, you can use the CHAR function:

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

Use chr(34) Code:

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

Result:

    Hi there, "joe"

Alternatively, you can use the CHAR function:

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

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

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

Alternatively, you can use the CHAR function:

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

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))"