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

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?

Fernando Barbosa · Accepted Answer · 2021-07-30 11:33:19Z

566

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"

edited Jul 30, 2021 at 11:33

Fernando Barbosa

1,1521 gold badge9 silver badges25 bronze badges

answered Oct 19, 2008 at 16:33

YonahW

16.8k9 gold badges44 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave DuPlantis · Accepted Answer · 2008-10-20 13:41:42Z

194

Alternatively, you can use the CHAR function:

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

answered Oct 20, 2008 at 13:41

Dave DuPlantis

6,6603 gold badges29 silver badges30 bronze badges

5 Comments

Ivan -Oats- Storck Over a year ago

This works more consistently for me than "" Maybe it's just because I have Mac Excel 2011

Amos M. Carpenter Over a year ago

@Ivanoats: Nope, it's not a Mac thing (using Windows here) - this does work more consistently; in my case, concatenating using & with a double quote just before or just after the ampersand didn't work using the double-double-quote method.

jamesnotjim Over a year ago

Works more consistently for me, as I often need to wrap quotation marks around a complicated string built up of multiple concatenated fields with interspersed punctuation. In my most recent case, to come up with something like this: "NameOfLocation, Street Address, City, State Zip"

GeneCode Over a year ago

This works 100% of the time. I tried "" before but it gave me error for ""-"". Using CHAR(34),"-",CHAR(34) works.

TheArchitecta May 13 at 10:14

This is the answer. BUT ALSO - I could not copy the cell and paste it into VSCode, I had to paste values into another cell and then copy that cell and paste it into VSCode. 2 Microsoft products working well. Bad +Bad = "BAD"

pnuts · Accepted Answer · 2015-12-17 03:07:04Z

27

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

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

= "x" hi

edited Dec 17, 2015 at 3:07

pnuts

59.7k11 gold badges92 silver badges141 bronze badges

answered Oct 4, 2012 at 23:41

Adel

2953 silver badges2 bronze badges

4 Comments

wh81752 Over a year ago

If this would be true, then please elaborate on the outcome of =concatenate("a"""b"). According to your observation, the outcome would be a"b. This is not the case. In fact this expression will not even be accepted by Excel.

KiwiSteve Over a year ago

you forgot the comma

Ben I. Over a year ago

The outer quotes in your example are just starting and ending the string. "" creates a quote character, not """.

mwfearnley Over a year ago

Two double-quotes will auto change to one double-quote: =CONCATENATE("<""x"">"," hi") -> <"x"> hi

tandy · Accepted Answer · 2014-08-13 16:46:39Z

8

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

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

answered Aug 13, 2014 at 16:46

tandy

1,9484 gold badges24 silver badges28 bronze badges

3 Comments

Brian C Over a year ago

Thank you! This totally helped me! I was struggling to properly format a JSON array in my cells.

infinitezero Dec 11, 2024 at 9:37

Why call CONCATENATE with 1 argument?

tandy Dec 18, 2024 at 4:31

@infinitezero In this case, the single argument contains dynamic concatenation (&), and Excel resolves it correctly, making it a convenient way to construct complex strings in a single step.

Community · Accepted Answer · 2017-05-23 12:26:20Z

7

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.

edited May 23, 2017 at 12:26

CommunityBot

11 silver badge

answered Jun 21, 2012 at 12:29

JimmyPena

8,7906 gold badges46 silver badges66 bronze badges

Comments

Chris · Accepted Answer · 2020-03-17 12:17:23Z

5

Use chr(34) Code:

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

Result:

    Hi there, "joe"

edited Mar 17, 2020 at 12:17

Chris

5,7683 gold badges24 silver badges43 bronze badges

answered Jul 31, 2015 at 13:32

Dave

772 silver badges4 bronze badges

Comments

Zon · Accepted Answer · 2017-05-18 09:26:45Z

3

Concatenate " as a ceparate cell:

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

D1 displays "text"

answered May 18, 2017 at 9:26

Zon

20k8 gold badges99 silver badges104 bronze badges

Comments

EdChum · Accepted Answer · 2012-05-26 00:13:16Z

1

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

edited May 26, 2012 at 0:13

EdChum

396k204 gold badges836 silver badges583 bronze badges

answered Aug 17, 2010 at 14:22

eric

111 bronze badge

2 Comments

Allain Lalonde Over a year ago

Don't know if this particular syntax is available in VBA. Give it a try.

Allain Lalonde Over a year ago

Looks like a yes: bytes.com/topic/access/insights/…

user4039065 · Accepted Answer · 2016-03-23 04:41:30Z

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.

Sam · Accepted Answer · 2013-02-13 17:58:08Z

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"

Karthick Gunasekaran · Accepted Answer · 2016-04-07 12:28:49Z

0

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

answered Apr 7, 2016 at 12:28

Karthick Gunasekaran

2,7231 gold badge17 silver badges26 bronze badges

Comments

Nathan S.R. · Accepted Answer · 2022-03-15 06:25:28Z

-1

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.

answered Mar 15, 2022 at 6:25

Nathan S.R.

573 bronze badges

2 Comments

chris neilsen Over a year ago

This is no different to the accepted answer, and add no additional value

Rob Clarke May 28 at 21:31

While not actually asked in the question, is when concatenating fields for SQL or other similar things it's useful. I recently needed to turn a set of phone numbers (like +16735552929) into a numeric with a leading zero to match contact numbers in a database. After stripping the + and country code, I had to add a leading zero, speech marks, and a comma. The formula was like this: =C2&",""""&B3&""""" And it requires four " (instead of three) to add a speech mark. It's easier than using and remembering CHAR(34).

pnuts · Accepted Answer · 2016-07-17 00:36:00Z

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.

Collectives™ on Stack Overflow

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

13 Answers 13

Comments

5 Comments

4 Comments

3 Comments

Comments

Comments

Comments

2 Comments

Comments

Comments

Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

13 Answers 13

Comments

5 Comments

4 Comments

3 Comments

Comments

Comments

Comments

2 Comments

Comments

Comments

Comments

2 Comments

Comments

Linked

Related