How to Add Single Quotes in Excel – 5 Methods

This is the sample dataset. To add single quotes to the city names:

Method 1 – Using the CHAR Function to Add Single Quotes in Excel

Use the CHAR function. It returns a character specified by a number. Here, CHAR(39) denotes the single quotes.

• Select C5.
• Enter the formula below.

• Press Enter.

This is the output.

• Drag down the Fill Handle to see the result in the rest of the cells.

Read More: How to Add Double Quotes in Excel Concatenate

Method 2 – Insert Single Quotes using the CONCATENATE Function

Use the CONCATENATE function. It joins two or more text strings into one string.

• Select C5.
• Enter the formula below.

• Press Enter.

This is the output.

• Drag down the Fill Handle to see the result in the rest of the cells.

Read More: How to Concatenate Single Quotes in Excel

Method 3 – Apply a Custom Format to Insert Single Quotes

• Copy the data in B5:B9 into the C5:C9.

• Right-click C5 and select Format Cells.

• In the Format Cells window, select Custom in Numbers.

• Enter‘@‘ in Type.
• Click OK.

• C5  displays single quotes.

• Apply the same method to C6:C9 or right-click C5 and select Format Painter.

• Brush it down to C9.

This is the output.

Read More: How to Add Single Quotes in Excel for Numbers

Method 4 – Using a Formula with the Ampersand Symbol

• Select C5.
• Enter the formula below.

• Press Enter.

This is the output.

• Drag down the Fill Handle to see the result in the rest of the cells.

Method 5 – Insert Single Quotes Using Excel VBA

• Copy B5:B9 and paste it in C5:C9.

• Go to the Developer tab and select Visual Basic in Code.

• Select Module in Insert.

• Enter the VBA code below:

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

• Click Run Sub or press F5.

• Click Run in the Macros.

This is the output.

Read More: How to Add Single Quotes and Comma in Excel Formula

Download Workbook

Download the sample workbook.

Related Articles

• How to Add Double Quotes and Comma in Excel with CONCATENATE
• How to Remove Single Quotes in Excel
• How to Remove Hidden Double Quotes in Excel

<< Go Back to Quotes in Excel | Concatenate Excel | Learn Excel