In this Excel lesson, you will teach yourself how to deal with single quote issues.

In Excel there is a problem that ‘yourtext’ (text inside single quotes) displays without leading single quote. There is an easy way to deal with this issue.

1. Right-click your cell.
2. Choose Format Cells to format a data type for the cell.
3. Click Custom Category.
4. Write down ‘@ (single quote and at) as a type.

That’s it. Your single quotes are displaying correctly since now.

How to quote a quote in Excel formula?

Excel formulas are used to do some simple calculations, to drive results using other cell values and sometimes we use some alphabetical strings in excel formulas which involve quotes : “.

E.g When the formula contain a string like:

a. My height is 70 ”

b. My height is 70 inches.

Now statement b can easily be inserted in an excel formula like = “My height is 70 inches”.

But for statement a we cannot write like: =” My height is 70 ” “.

It will return formula errors:

So whenever we have quotes in a formula we have to understand that we have to escape the double quotes with double apostrophes to let Excel read it properly.

So the correct value: = ” My height is 70 “” “.

Using char 39

Another way to do this is using CHAR(39) for single quotes. The CHAR function allows you to enter a specific character code and display the corresponding character in a cell.

Like we used quotes before apostrophes in previous example, here we are using char(39) instead of writing the quotes:

=” My height is 70 ” & CHAR(39)

And the result will be same: My height is 70 ”

The code for a double quote is 34, so you can use the following formula to display a double quote:

=CHAR(34) & “Your text here” & CHAR(34)

Related posts:

How to Get Data from another Sheet? Time in milliseconds in Excel How to Sum only Positive / Negative Numbers in Excel