How to Concatenate Single Quotes in Excel (5 Easy Ways)

In this article, we will learn to concatenate single quotes in Excel. Concatenating single or double quotes in Excel becomes confusing for various reasons. Today, we will demonstrate 5 easy methods. Using these methods, you can easily concatenate single quotes in Excel. So, without further delay, let’s start the discussion.


Download Practice Book

You can download the practice book from here.


5 Easy Ways to Concatenate Single Quotes in Excel

To explain these methods, we will use a dataset that contains information about the Name and Departments of some employees. We will concatenate single quotes in the Department name and store them in the range D5:D9. Let’s follow the methods in the below sections to find out the results.


1. Use Ampersand to Concatenate Single Quotes in Excel

In the first method, we will use the Ampersand (&) operator to concatenate single quotes in Excel. You need to be careful while typing the formula in a cell. Let’s follow the steps below to see how we can implement the formula.

STEPS:

  • In the beginning, select Cell D5 and type the formula below:
="'"&C5&"'"

Use Ampersand to Concatenate Single Quotes in Excel

Note: In this formula, we have used a double quote followed by a single quote, then again used a double quote and then, the ampersand operator. So, the general form of the formula can be written as:

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

  • Secondly, hit Enter and drag the Fill Handle down.

Use Ampersand to Concatenate Single Quotes in Excel

  • Finally, you will see concatenated single quotes like the picture below.

Use Ampersand to Concatenate Single Quotes in Excel

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

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


2. Add Single Quotes with Excel CHAR Function

We can use the CHAR function to add single quotes in Excel. This formula is simple and there is no confusion. In Excel, CHAR(39) denotes single quotes. Let’s observe the steps below to learn the method easily.

STEPS:

  • Firstly, type the formula below in Cell D5:
=CHAR(39)&C5&CHAR(39)

Add Single Quotes with Excel CHAR Function

In this formula, CHAR(39) represents single quotes. We have used the Ampersand (&) operator to concatenate single quotes with Cell C5.

  • After that, press Enter and use the Fill Handle to copy the formula down.

Add Single Quotes with Excel CHAR Function

  • In the end, you will get the desired results.

Add Single Quotes with Excel CHAR Function

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

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

Read More: How to Add Single Quotes and Comma in Excel Formula (4 Ways)


3. Combine Excel CONCATENATE and CHAR Functions to Insert Single Quotes

Another way to insert single quotes in Excel is to use the combination of the CONCATENATE and CHAR functions. The advantage of this combination is that you don’t need to use the Ampersand (&) operator. So, let’s pay attention to the steps below to see how we can combine the CONCATENATE and CHAR functions.

STEPS:

  • First of all, select Cell D5 and type the formula below:
=CONCATENATE(CHAR(39),C5,CHAR(39))

Combine Excel CONCATENATE and CHAR Functions to Insert Single Quotes

In this formula, we have used the CONCATENATE function. The CONCATENATE function joins different text strings into a single string. You need to separate the strings by a comma inside the formula. Also, you can use the CONCAT function instead of the CONCATENATE function. Both will show the same result.

  • In the second step, press Enter and drag down the Fill Handle.

Combine Excel CONCATENATE and CHAR Functions to Insert Single Quotes

  • In the end, the results will look like the picture below.

Combine Excel CONCATENATE and CHAR Functions to Insert Single Quotes

Read More: How to Add Single Quotes in Excel for Numbers (3 Easy Methods)


4. Add Single Quotes in Excel Using Format Cells Dialog Box

Interestingly, we can also add single quotes using the Format Cells dialog box. This process is also straightforward. Here, we will not use any helper column. We can directly apply the method to the desired cells.

Add Single Quotes in Excel Using Format Cells Dialog Box

Let’s follow the steps below to see how we can use the Format Cells dialog box.

STEPS:

  • In the first place, select the cells where you want to add single quotes.
  • Here, we have selected the range C5:C9.

Add Single Quotes in Excel Using Format Cells Dialog Box

  • Secondly, rightclick on the selected cells to open the Context Menu.
  • Select Format Cells from there.

Add Single Quotes in Excel Using Format Cells Dialog Box

  • In the Format Cells window, select Number and then, select Custom.
  • Then, write ‘@’ in the Type field.
  • Click OK to proceed.

Add Single Quotes in Excel Using Format Cells Dialog Box

Note: If you want to add single quotes for numbers, then you need to write ‘#’ 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.

  • In the end, the dataset will look like the picture below.

Add Single Quotes in Excel Using Format Cells Dialog Box

Read More: How to Convert Column to Comma Separated List With Single Quotes


5. Apply VBA to Concatenate Single Quotes in Excel

In Excel, VBA gives users the opportunity to do many difficult tasks easily. We can also use a VBA code to concatenate single quotes in Excel. We will discuss more in the following steps. So, let’s pay attention to the steps below to learn this method.

STEPS:

  • Firstly, go to the Developer tab and select Visual Basic. It will open the Visual Basic window.

  • Secondly, select Insert.
  • Then, select Module in the Visual Basic This will open the Module window.

  • Now, type the code in the Module window:
Sub Concatenate_Single_Quotes()
Range("C5:C9").NumberFormat = "'@'"
End Sub

This VBA code will add single quotes in the range C5:C9. You need to change the range according to your needs. Also, if your range contains numbers, then you need to type “ ‘#’ ” in place of “ ‘@’ ”.

  • Press Ctrl + S to save the code.
  • After that, you can press the F5 key to run the code.
  • Alternatively, go to the Developer tab and select Macros.

  • In the following step, select the code and Run it from the Macro window.

  • Finally, you will see results like the picture below.

Read More: How to Add Double Quotes and Comma in Excel with CONCATENATE


Conclusion

In this article, we have discussed 5 easy methods to Concatenate Single Quotes in Excel. I hope this article will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. You can also visit the ExcelDemy website for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo