Md. Shamim Reza
This article illustrates how to add single quotes in excel for numbers. You may need to wrap some numbers in your dataset in single quotes. This article will show you 3 simplest ways to do that in excel. So, have a quick look through the article to know that.
Download Practice Workbook
You can download the practice workbook from the download button below.
3 Ways to Add Single Quotes in Excel for Numbers
Assume you have the following dataset. It contains monthly sales made by sales representatives. Now you need to wrap the sales in single quotes.
Follow the methods below to be able to do that.
1. Add Single Quotes for Numbers with Format Cells Option
You can apply cell formatting to add single quotes around those numbers. Follow the steps below to do that.
📌 Steps
- First, select the range D5:D10 as shown below.
- Then press CTRL+1 to open the Format Cells dialog box. Next, select the Custom number format from the Number tab as shown below. After that, enter ‘#’ in the Type field for the custom formatting. Finally press Enter or click OK.
- After that, you will see the following result.
Read More: How to Concatenate Single Quotes in Excel (5 Easy Ways)
2. Utilize Ampersand (&) Operator
Alternatively, you can use a simple formula to add single quotes around numbers. You need to enter the following formula in cell E5 and drag the Fill Handle icon to do that. The only problem is that this will convert the numbers to texts.
="'"&D5&"'"
Read More: How to Add Single Quotes and Comma in Excel Formula (4 Ways)
3. Use a VBA Code to Add Single Quotes for Numbers
You can also use a simple VBA code to add single quotes in excel for numbers. Follow the steps below to do that.
📌 Steps
- First, press ALT+F11 to open the VBA window. Then, select Insert >> Module to create a new module.
- Next, copy the following code.
Sub AddSingleQuotes()
Range("D5:D10").NumberFormat = "'#'"
End Sub- After that, paste the code onto the blank module.
- Now, keep the cursor on the code and press F5. Finally, you will get the same result as earlier.
Read More: How to Convert Column to Comma Separated List With Single Quotes
Things to Remember
- Using the formula will convert the numbers to texts.
- You need to use a different range in the code based on your dataset.
Conclusion
Now you know how to add single quotes in excel for numbers. Do you have any further queries or suggestions? Please let us know using the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.
