How to display a single quote in a cell?

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.

mytext single quotation problem

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

quote a quote

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

quote a quote transpose

It will return formula errors:

quote error

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 “” “.

quote a quote proper formula

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.

See also  How to Make an Estimate in Excel

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 ”

quote a quote formula proper formula

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)