Mursalin Ibne Salehin
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&"'"
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.
- Finally, 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”.
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)
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.
- In the end, you will get the desired results.
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))
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.
- In the end, the results will look like the picture below.
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.
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.
- Secondly, right–click on the selected cells to open the Context Menu.
- Select Format Cells from there.
- In the Format Cells window, select Number and then, select Custom.
- Then, write ‘@’ in the Type field.
- Click OK to proceed.
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.
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.
