How to Add Single Quotes in Excel – 5 Methods

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

How to Add Single Quotes in Excel

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

CHAR(39) denotes the single quotes.

  • Select C5.
  • Enter the formula below.
=CHAR(39)&B5&CHAR(39)

Use CHAR Function to Add Single Quotes in Excel

  • Press Enter.

This is the output.

Use CHAR Function to Add Single Quotes in Excel

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

Note: Use this formula to add double quotes in excel.

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

Read More: How to Add Double Quotes in Excel Concatenate


Method 2 – Insert Single Quotes using the CONCATENATE Function

  • Select C5.
  • Enter the formula below.
=CONCATENATE("'",B5,"'")

Insert Single Quotes with CONCATENATE Function

 

  • Press Enter.

This is the output.

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

  • 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 C5:C9.

Apply Custom Format to Add Single Quotes in Excel

  • Right-click C5 and select Format Cells.

Apply Custom Format to Add Single Quotes in Excel

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

Apply Custom Format to Add Single Quotes in Excel

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

Apply Custom Format to Add Single Quotes in Excel

C5 displays single quotes.

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

Apply Custom Format to Add Single Quotes in Excel

  • Brush it down to C9.

This is the output.

Note: You can also use \’@\’ in Custom Format.

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.
="'"&B5&"'"

Formula with Ampersand Symbol to Attach Single Quotes 

  • Press Enter.

This is the output.

Formula with Ampersand Symbol to Attach Single Quotes 

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

Add Single Quotes Using Excel VBA

  • 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

Add Single Quotes Using Excel VBA

  • Click Run Sub or press F5.

  • Click Run in the Macros.

Add Single Quotes Using Excel VBA

This is the output.

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


Download Workbook

Download the sample workbook.


 

Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo