Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.

add single quotes in excel for numbers

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&"'"

add single quotes in excel for numbers

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.

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


Related Articles

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

ExcelDemy
Logo