# How to Concatenate Single Quotes in Excel (5 Methods)

## Dataset Overview

To demonstrate these methods, we’ll use a dataset containing information about employee names and departments. Our goal is to concatenate single quotes around the department names and store the results in the range D5:D9.

### Method 1 – Use Ampersand (&) to Concatenate Single Quotes

STEPS

• Select Cell D5 and enter the formula:
`="'"&C5&"'"`

Note: This formula combines a double quote, a single quote, the department name (from cell C5), and another single quote. The result will be a department name enclosed in single quotes.

So, the general form of the formula can be written as:

=Double Quote Single Quote Double Quote &C5& Double Quote Single Quote Double Quote

• Press Enter and drag the fill handle down to apply the formula to other cells.

• You will see concatenated single quotes like the picture below.

Note: To concatenate double quotes, type double quotes in place of single quotes and add two new double quotes on both sides of the formula. So, the formula becomes:

=””””&C5&””””

The result of this formula will be: “Sales”.

### Method 2 – Add Single Quotes with Excel CHAR Function

STEPS

• In cell D5, enter the formula:
`=CHAR(39)&C5&CHAR(39)`

Here, CHAR(39) represents a single quote. The formula concatenates the single quotes with the department name from cell C5.

• Press Enter and use the fill handle to copy the formula down.

• You will get the desired results.

Note: To add double quotes, enter the formula below:

=CHAR(34)&C5&CHAR(34)

### Method 3 – Combine Excel CONCATENATE and CHAR Functions

STEPS

• Select cell D5 and enter the formula:
`=CONCATENATE(CHAR(39),C5,CHAR(39))`

This formula achieves the same result as the previous method. The CONCATENATE function joins text strings, and CHAR(39) represents a single quote.

• Press Enter and drag down the fill handle.

• The results will look like the picture below.

### Method 4 – Add Single Quotes Using Format Cells Dialog Box

STEPS

• Select the cells (e.g., C5:C9) where you want to add single quotes.

• Right-click and choose Format Cells.

• In the Format Cells window, select Number and then choose Custom.
• Enter ‘@’ in the Type field.
• Click OK.

Note: If you want to add single quotes for numbers, then you need to enter ‘#’ in the Type field. For example, if you have 2323 in a cell, then you need to write ‘#’ in place of ‘@’ in the Type field.

• The selected cells will display the department names enclosed in single quotes.

Read More: How to Remove Single Quotes in Excel

### Method 5 – Using VBA to Concatenate Single Quotes in Excel

In Excel, VBA (Visual Basic for Applications) provides users with the capability to perform complex tasks more easily. We can leverage VBA to concatenate single quotes in Excel. Let’s walk through the steps for this method:

STEPS

• Go to the Developer tab and select Visual Basic. This action will open the Visual Basic window.

• Click on Insert in the Visual Basie window.
• Choose Module.  This will open a new Module window.

• In the Module window, enter the following code:
``````Sub Concatenate_Single_Quotes()
Range("C5:C9").NumberFormat = "'@'"
End Sub``````

• This VBA code sets the number format of the specified range (C5:C9) to display values with single quotes around them.
• If your range contains numbers, enter “#” instead of “@” to add single quotes around numeric values.
• Press Ctrl + S to save the code.
• Press F5 (to execute the entire subroutine).
• Alternatively, go to the Developer tab and select Macros and run the code from the Macro window.

• The specified range (C5:C9) will now display values enclosed in single quotes.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF