How to add single quotes in Excel

You can watch a video tutorial here.

When you type a single quote in a cell in Excel and follow it with some letters or numbers, the quote does not appear. This is because in Excel the single quote is used to indicate that the value being entered into the cell is a text value, and not numeric. Single quotes are very often used to convert numbers into the text data type i.e. to tell Excel to treat a number as a text value. Numbers converted into the text data type cannot be used for calculations e.g. phone numbers and zip codes. Adding a single quote within the text does not pose a problem, it is only when the single quote precedes the text. 

There will be occasions where you will need to add single quotes to the head of your text, for example, when you are creating a list of quotes from famous people. There are 4 ways of doing this:

1. Type two single quotes: this method involves editing each cell
2. CONCAT() function: the CONCAT() function joins single quotes to the text
   1. Syntax: CONCAT(text1, text2, …..textn)
      1. text1, text2: the pieces of text to be joined
3. Format the cells: this uses the formatting tools to change the format of the cell
4. Ampersand (&) operator: similar to the CONCAT() function), this operator joins single quotes to the text 

Option 1 – Type 2 single quotes

Step 1 – Edit the cell

• Select the cell in which the single quote is to be added
• Enable the cell for editing by pressing F2 or by placing the cursor in the formula bar
• Add 2 single quotes (‘’) to the head of the text
• Add 1 single quote to the end of the text

Step 2 – Check the result

• Press Enter
• The text will be within single quotes
• Select the cell and check the formula bar which will show 2 single quotes at the head of the text

Option 2 – Use the CONCAT() function

Step 1 – Create the formula around the text

• Select an empty cell in a column in the same row as the text to which the single quote is to be added
• Type the formula using cell references:

= CONCAT(“’”,Quote,”’”)

Note: The single quote is enclosed in double quotes to indicate that it is text.

Step 2 – Copy the formula

• Using the fill handle from the first cell, drag the formula to the remaining cells

OR

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

Step 3 – Copy the newly created text

• Select the text to which the single quotes have been added and press Ctrl+C or right-click and select Copy from the context menu

Step 4 – Open the Paste Special box

• Select the original text that does not have the single quotes
• Open the Paste Special window by right-clicking and selecting Paste Special from the context menu

OR

Go to Home > Clipboard > Paste > Paste Special

OR

Press Alt+E+S

Step 5 – Paste only the values

• Select Values
• Click OK

Step 6 – Check the result

• The text with single quotes overwrites the text without the quotes and the formula has been removed
• Delete the temporary column

Option 3 – Format the cells

Step 1 – Open the Format Cells window

• Select the range of cells to which single quotes have to be added
• Right-click and select Format Cells  from the context menu

OR

Go to Home > Number  and click on the arrow to expand the menu

OR

Go to Home > Cells > Format > Format Cells

OR

Press Ctrl+1

Step 2 – Create a custom format

• Under the Number tab, select Custom
• Type: ‘@’
• Click OK

Step 3 – Check the result

• The text is formatted to be within single quotes

Option 4 – Use the Ampersand (&) operator 

Step 1 – Create the formula around the text

• Select an empty cell in a column in the same row as the text to which the single quote is to be added
• Type the formula using cell references:

= “’”& Quote & ”’”

Note: The single quote is enclosed in double quotes to indicate that it is text.

Step 2 – Copy the formula

• Using the fill handle from the first cell, drag the formula to the remaining cells

OR

3. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
4. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

Step 3 – Copy the newly created text

• Select the text to which the single quotes have been added and press Ctrl+C or right-click and select Copy from the context menu

Step 4 – Open the Paste Special box

• Select the original text that does not have the single quotes
• Open the Paste Special window by right-clicking and selecting Paste Special from the context menu

OR

Go to Home > Clipboard > Paste > Paste Special

OR

Press Alt+E+S

Step 5 – Paste only the values

• Select Values
• Click OK

Step 6 – Check the result

• The text with single quotes overwrites the text without the quotes and the formula has been removed
• Delete the temporary column