How to Add Single Quotes in Excel (5 Easy Methods)

When we work on an excel worksheet, often we need to add some cells as a single quote. This single quote or an apostrophe means that the cell is a Text in excel. Even if a number is shown with a single quote generally cannot be used in calculations. In this article, we will learn how to add single quotes in excel with easy methods.

How to Add Single Quotes in Excel: 5 Easy Methods

For example, here is a very simple dataset with 5 different city names. We will add single quotes in these names using 5 easy methods.

1. Use CHAR Function to Add Single Quotes in Excel

In this first method, we will use the CHAR function to add single quotes in excel. In general, the CHAR function is a type of Text function. It returns a character specified by a certain number. It is mostly used in coding page numbers or breaking lines into texts.

Here, CHAR(39) denotes the single quotes.

Let’s follow to process below:

First, select cell C5 in the dataset.

Then, insert the CHAR formula in this cell.

=CHAR(39)&B5&CHAR(39)

Now, press Enter.
That’s it, we have successfully added single quotes for cell B5.

Finally, use the Auto Fill tool to apply the same formula in the cells C6:C9.

Note: Use this formula for adding double quotes in excel.

=CHAR(34)&cellnumber&CHAR(34)

2. Insert Single Quotes with CONCATENATE Function

Another useful method to insert single quotes is with the CONCATENATE function. The CONCATENATE function is also a category of the Text function in excel. It is beneficial for joining two or more text strings into one string.

Just follow the simple steps below:

First, select cell C5.
Following, insert the CONCATENATE formula.

=CONCATENATE("'",B5,"'")

After that, press Enter.
Here, the reference cell B5 has been added within single quotes.

In the end, insert a similar formula in cells C6:C9 or you can just use the AutoFill tool in excel.

Read More: How to Concatenate Single Quotes in Excel

3. Apply Custom Format to Insert Single Quotes

Applying Custom Format is one of the easiest methods to add single quotes in excel. Let’s see how it works:

In the beginning, copy the data of the cells B5:B9 into the cells C5:C9.

Then, right-click on cell C5 and select Format Cells.

After that, a new Format Cells window pops up.
Here, select Custom from the Numbers section.

Now add this symbol ‘@‘ inside the Type box.
Then, press OK.

Finally, you can see that cell C5 is confined to single quotes.

Next, apply the same method to cells C6:C9.
Otherwise, right-click on cell C5 and select the Format Painter option.

Following, brush it down over the cells C6:C9.
The final output will add single quotes in these cells.

Note: You can also apply this symbol \’@\’ as the Custom Format.

4. Formula with Ampersand Symbol to Attach Single Quotes

Another easy method to attach single quotes is to use a formula with an ampersand symbol. Follow the steps below:

First, select cell C5 and insert this formula:

="'"&B5&"'"

Then, press Enter.
Finally, it will show the text inside single quotes like this:

Lastly, apply the same formula in cells C6:C9.
Otherwise, use the excel Auto Fill tool to add the formula in each cell.

5. Insert Single Quotes Using Excel VBA

The last method we will discuss is to add single quotes using Excel VBA Macro.

At first, copy the cell range B5:B9 in the cell range C5:C9.

Then, select Visual Basic under the Code group from the Developer tab.

Afterward, a new window appears.
Here, select Module from the Insert section.

Now, add the VBA code below on the blank page:

Sub AddQuote()
 Dim myCl As Range
     For Each myCl In Selection
         If myCl.Value <> "" Then
             myCl.Value = Chr(39) & Chr(39) & myCl.Value & Chr(39)
         End If
     Next myCl
 End Sub