CODEWITHSUNDEEP
excelexcel-formula
Allain Lalonde
Allain Lalonde

Reputation: 93348

How to create a string or formula containing double quotes in Excel?

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?

Upvotes: 372

Views: 654547

Answers (13)

Nathan S.R.
Nathan S.R.

Reputation: 47

The following formula works on Excel as well as Numbers (on MAC) :

= "Maurice " & """" & "The Rocket" & """" & " Richard"

Use & """" & to get a single double quote surrounding your string.

Upvotes: -2

YonahW
YonahW

Reputation: 16520

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"

Upvotes: 547

Dave
Dave

Reputation: 77

Use chr(34) Code:

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

Result:

    Hi there, "joe"

Upvotes: 5

Zon
Zon

Reputation: 19880

Concatenate " as a ceparate cell:

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

D1 displays "text"

Upvotes: 3

pnuts
pnuts

Reputation: 59460

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:

SO216616 example

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.

Upvotes: -2

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

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

Upvotes: 0

user4039065
user4039065

Reputation:

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.

Upvotes: 1

Adel
Adel

Reputation: 295

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

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

= "x" hi

Upvotes: 27

tandy
tandy

Reputation: 1948

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

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

Upvotes: 8

Sam
Sam

Reputation: 9

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"

Upvotes: 0

JimmyPena
JimmyPena

Reputation: 8754

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.

Upvotes: 7

eric
eric

Reputation: 11

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

Upvotes: 1

Dave DuPlantis
Dave DuPlantis

Reputation: 6572

Alternatively, you can use the CHAR function:

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

Upvotes: 190

Related Questions