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.

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.
Adjust the range according to your specific needs.
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.