Excel Formula – Create String or Formula Containing Double Quotes

excelexcel-formula

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?

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"

I find the easiest way is to double up on the quotes to handle a quote.

Worksheets("Sheet1").Range("A1").Formula = "IF(Sheet1!A1=0,"""",Sheet1!A1)"

Some people like to use CHR(34)*:

Worksheets("Sheet1").Range("A1").Formula = "IF(Sheet1!A1=0," & CHR(34) & CHR(34) & ",Sheet1!A1)"

*Note: CHAR() is used as an Excel cell formula, e.g. writing "=CHAR(34)" in a cell, but for VBA code you use the CHR() function.

You need to change the formula to

ActiveCell.Formula = "=IF($W9=""A"",1,IF($W9=""B"",2, IF($W9=""C"",3,0)))"

So, first you need to double quote and in your case you also need to count the number of "(" and ")"