Sanjida Mehrun Guria
When we work on an excel worksheet, often we need to add some cells as a single quote. This single quote or an apostrophe means that the cell is a Text in excel. Even if a number is shown with a single quote generally cannot be used in calculations. In this article, we will learn how to add single quotes in excel with easy methods.
Download Workbook
Get the sample workbook to practice.
5 Easy Methods to Add Single Quotes in Excel
For example, here is a very simple dataset with 5 different city names. We will add single quotes in these names using 5 easy methods.
1. Use CHAR Function to Add Single Quotes in Excel
In this first method, we will use the CHAR function to add single quotes in excel. In general, the CHAR function is a type of Text function. It returns a character specified by a certain number. It is mostly used in coding page numbers or breaking lines into texts.
Here, CHAR(39) denotes the single quotes.
Let’s follow to process below:
- First, select cell C5 in the dataset.
- Then, insert the CHAR formula in this cell.
=CHAR(39)&B5&CHAR(39)
- Now, press Enter.
- That’s it, we have successfully added single quotes for cell B5.
- Finally, use the Auto Fill tool to apply the same formula in the cells C6:C9.
Read More: How to Add Double Quotes in Excel Concatenate (5 Easy Ways)
2. Insert Single Quotes with CONCATENATE Function
Another useful method to insert single quotes is with the CONCATENATE function. The CONCATENATE function is also a category of the Text function in excel. It is beneficial for joining two or more text strings into one string.
Just follow the simple steps below:
- First, select cell C5.
- Following, insert the CONCATENATE formula.
=CONCATENATE("'",B5,"'")
- After that, press Enter.
- Here, the reference cell B5 has been added within single quotes.
- In the end, insert a similar formula in cells C6:C9 or you can just use the AutoFill tool in excel.
Read More: How to Concatenate Single Quotes in Excel (5 Easy Ways)
3. Apply Custom Format to Insert Single Quotes
Applying Custom Format is one of the easiest methods to add single quotes in excel. Let’s see how it works:
- In the beginning, copy the data of the cells B5:B9 into the cells C5:C9.
- Then, right-click on cell C5 and select Format Cells.
- After that, a new Format Cells window pops up.
- Here, select Custom from the Numbers section.
- Now add this symbol ‘@‘ inside the Type box.
- Then, press OK.
- Finally, you can see that cell C5 is confined to single quotes.
- Next, apply the same method to cells C6:C9.
- Otherwise, right-click on cell C5 and select the Format Painter option.
- Following, brush it down over the cells C6:C9.
- The final output will add single quotes in these cells.
Read More: How to Add Single Quotes in Excel for Numbers (3 Easy Methods)
4. Formula with Ampersand Symbol to Attach Single Quotes
Another easy method to attach single quotes is to use a formula with an ampersand symbol. Follow the steps below:
- First, select cell C5 and insert this formula:
="'"&B5&"'"
- Then, press Enter.
- Finally, it will show the text inside single quotes like this:
- Lastly, apply the same formula in cells C6:C9.
- Otherwise, use the excel Auto Fill tool to add the formula in each cell.
5. Insert Single Quotes Using Excel VBA
The last method we will discuss is to add single quotes using Excel VBA Macro.
- At first, copy the cell range B5:B9 in the cell range C5:C9.
- Then, select Visual Basic under the Code group from the Developer tab.
- Afterward, a new window appears.
- Here, select Module from the Insert section.
- Now, add the VBA code below on the blank page:
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
- Then, click the Run Sub button or press F5 on your keyboard.
- Following, click on Run in the new Macros window.
- Finally, the selected cells have single quotes.
Read More: How to Convert Column to Comma Separated List With Single Quotes
Conclusion
Hope this was an efficient article for you on how to add single quotes in excel with 5 easy methods. Let us know if you have suggestions on this. Follow ExcelDemy for more excel related blogs.
