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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Add Single Quotes in Excel for Numbers: 3 Easy Ways

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.

add single quotes in excel for numbers

Read More: How to Concatenate Single Quotes in Excel


2. Utilize Ampersand (&) Operator

Alternatively, you can use a simple formula to add single quotes around numbers. You must enter the formula in cell E5 and drag the Fill Handle icon to do that. The only problem is that this will convert the numbers to text.

="'"&D5&"'"

add single quotes in excel for numbers

Read More: How to Add Single Quotes and Comma in Excel Formula


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.

add single quotes in excel for numbers

  • Now, keep the cursor on the code and press F5. Finally, you will get the same result as earlier.

Read More: How to Add Single Quotes in Excel


Things to Remember

  • Using the formula will convert the numbers to text.
  • You need to use a different range in the code based on your dataset.

Download Practice Workbook

You can download the practice workbook from the download button below.


Conclusion

Now you know how to add single quotes in Excel for numbers. Do you have any further queries or suggestions? Let us know using the comment section below. Stay with us and keep learning.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo