Problem
How do I display double quotation marks in an Excel formula?
Background
Usually, the purpose of double quotes in an Excel formula is to indicate the beginning and end of a string of text. The characters themselves aren’t displayed.
For example, if we type the following formula:
="Nothing special, just some text"
The cell will display the following: Nothing special, just some text
But if we want to create a formula that displays double quotes within the formula result, we need to get a bit clever… Let’s look at two ways to do it.
Solution
Download the example workbook to follow along.
Using an escape character (“)
When we type two double quote characters in a formula, we tell Excel that we want to display a double quote character. The first character serves as an escape character and the second represents the character that we want to display.
So instead of having the double quote do its normal job of indicating the start or end of a text string, we type an escape character to tell Excel to do something different with the double quote, i.e. to display it like normal text.
The escape character just happens to be another double quote, so we type two double quotes in a row.
Example 1
If we type the following formula:
="Nothing special, just some ""boring"" text"
The cell will display: Nothing special, just some “boring” text
Mission accomplished!
P.S. This illustrates the point, but it’s a silly example, as we don’t need to write it as a formula. Instead of entering it as a formula we could have just typed the text as we want it displayed: Nothing special, just some "boring" text
Example 2
Pro tip: Concatenate various bits in a single cell using an ampersand (& sign). In this example we have a formula that glues together a text string AND a cell reference AND another text string. Refer to cell C24 in the example workbook.
Let’s look at a trickier example – we now want to show a “ character in a formula that refers to another cell. Consider the following example:
="Nothing special, just some """ & F23 & """ text"
The cell will again display: Nothing special, just some “boring” text
They can get tricky to count, but there are three double-quotes after the word “some”. The first one is the escape character, the second one is the character that we want to display, and the third one is the character indicating the end of the first text string.
We then have a reference to a cell, followed by another text string. In this last text string there are again three double quotes. The first being the escape character, the second is the character that we want to display and the third tells Excel it’s the end of the text string.
Example 3
If we want to concatenate just a double quote character with a text string or cell reference, we need to place four double quotes in a row!
Consider the following formula:
=G23 & """"
If cell G23 contains the value 12, the result of the formula will be: 12″
The first double quote tells Excel that this is where the string of text starts. The second and third are the escape character and the character we want to display, and the fourth double quote tells Excel where the text string ends.
Using an escape character (“)
As we can see in the examples above, the multiple double quotes can become rather hard to count. Especially in longer, more complicated formulas. Sometimes it makes more sense to use the CHAR function instead of typing two, three or even four double quotes.
Example 4The CHAR() function is used to convert a number to a character. CHAR(34) represents a double quote (“) in the Windows as well as the Mac OS character sets, and can be embedded in a formula as follows:
=”Nothing special, just some ” & CHAR(34) & F17 & CHAR(34) & ” text”
In this example, the red bits swap out the red bits in Example 2, as follows:
="Nothing special, just some """ & F23 & """ text"
=”Nothing special, just some ” & CHAR(34) & F17 & CHAR(34) & ” text”
