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

User · Answer

Alternatively  you can use the CHAR function      Maurice    amp  CHAR 34   amp   Rocket   amp  CHAR 34   amp    Richard

User · Answer

Alternatively  you can use the CHAR function      Maurice    amp  CHAR 34   amp   Rocket   amp  CHAR 34   amp    Richard

User · Answer

Concatenate   as a ceparate cell       A     B     C   D 1       text         CONCATENATE A1  B1  C1    D1 displays  text

User · Answer

I use a function for this  if the workbook already has VBA    Function Quote inputText As String  As String   Quote   Chr 34   amp  inputText  amp  Chr 34  End Function   This is from Sue Mosher s book  Microsoft Outlook Programming   Then your formula would be     Maurice   amp Quote  Rocket   amp   Richard   This is similar to what Dave DuPlantis posted

User · Answer

Alternatively  you can use the CHAR function      Maurice    amp  CHAR 34   amp   Rocket   amp  CHAR 34   amp    Richard

User · Answer

Alternatively  you can use the CHAR function      Maurice    amp  CHAR 34   amp   Rocket   amp  CHAR 34   amp    Richard

User · Answer

Three double quotes        x          x  Excel will auto change to one double quote  e g       CONCATENATE    x      hi          x  hi

User · Answer

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

User · Answer

Concatenate   as a ceparate cell       A     B     C   D 1       text         CONCATENATE A1  B1  C1    D1 displays  text

User · Answer

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

User · Answer

Have you tried escaping with a double-quote      Maurice   The Rocket   Richard

User · Answer

Have you tried escaping with a double-quote      Maurice   The Rocket   Richard

User · Answer

will this work for macros using  Formula     THEFORMULAFUNCTION  STUFF    so it would be like  will this work for macros using  Formula     THEFORMULAFUNCTION CHAR 34   amp  STUFF  amp  CHAR 34

User · Answer

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

User · Answer

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    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   amp  CHAR 34   amp   STUFF   amp  CHAR 34    2  THEFORMULAFUNCTION  STUFF

User · Answer

Use  chr 34  Code       Joe    Hi there     amp  Chr 34   amp   Joe   amp  Chr 34      ActiveCell Value   Joe   Result       Hi there   joe

User · Answer

In the event that you need to do this with JSON    CONCATENATE      service        field      amp A2 amp

User · Answer

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

User · Answer

Have you tried escaping with a double-quote      Maurice   The Rocket   Richard

User · Answer

will this work for macros using  Formula     THEFORMULAFUNCTION  STUFF    so it would be like  will this work for macros using  Formula     THEFORMULAFUNCTION CHAR 34   amp  STUFF  amp  CHAR 34

User · Answer

Maurice   amp    TheRocker    amp   Richard

User · Answer

I use a function for this  if the workbook already has VBA    Function Quote inputText As String  As String   Quote   Chr 34   amp  inputText  amp  Chr 34  End Function   This is from Sue Mosher s book  Microsoft Outlook Programming   Then your formula would be     Maurice   amp Quote  Rocket   amp   Richard   This is similar to what Dave DuPlantis posted

User · Answer

Have you tried escaping with a double-quote      Maurice   The Rocket   Richard

User · Answer

In the event that you need to do this with JSON    CONCATENATE      service        field      amp A2 amp

User · Answer

Three double quotes        x          x  Excel will auto change to one double quote  e g       CONCATENATE    x      hi          x  hi

User · Answer

Maurice   amp    TheRocker    amp   Richard

User · Answer

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    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   amp  CHAR 34   amp   STUFF   amp  CHAR 34    2  THEFORMULAFUNCTION  STUFF

User · Answer

Use  chr 34  Code       Joe    Hi there     amp  Chr 34   amp   Joe   amp  Chr 34      ActiveCell Value   Joe   Result       Hi there   joe

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

Examples related to excel

Examples related to excel-formula