How to Add Quotes in Excel (Single and Double Quotes)

In this Excel tutorial, we will show how to add single quotes as well as double quotes in Excel. The most common ways to add quotes are by making use of the formula bar using functions like CHAR or CONCATENATE and the Ampersand operator. We can also apply a custom formula from the Format Cells box. In addition to that, we can use VBA to add quotes in Excel.

We have used Microsoft 365 when preparing this article, but the operations apply to all Excel versions. 

While working in Microsoft Excel, quotes are primarily used to distinguish text values from other types of data. A quote indicates that the content inside it should be treated like a text string. They are commonly used when entering text, using formulas, or writing VBA code in Excel.

Download Practice Workbook

For a better understanding, you can download the practice workbook here.

What Is the Key Difference Between Single and Double Quotes in Excel?

In Excel, double quotes are used for formulas, escaping characters, and representing text values, while single quotes are used to display quotes.

How to Add Single Quotes in Excel

If you want to add single quotes in Excel, there are several ways you can do it. The most common ways are: using the CHAR function, applying the CONCATENATE function, or using a custom formula among others.

1. Use CHAR Function to Add Single Quotes in Excel

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

In this case, CHAR(39) denotes single quotes.

• Insert the following CHAR formula in cell C5 and press Enter.
• Then use the AutoFill tool to apply the same formula in the cells C6:C9.

2. Insert Single Quotes with CONCATENATE Function

You can also insert single quotes using the CONCATENATE function. This function is beneficial for joining two or more text strings into one.

• Insert the following CONCATENATE formula in cell C5 and press Enter.
• Then use the AutoFill tool to apply the same formula in the cells C6:C9.

3. Use Formula with Ampersand Symbol to Add Single Quotes

You can also use formulas with the Ampersand(&) symbol to add single quotes. Here are the steps:

• Insert the following formula in cell C5 and press Enter.
• Then use the AutoFill tool to apply the same formula in the cells C6:C9.

4. Apply Custom Format to Insert Single Quotes

You can also apply a custom format to insert single quotes. To do so,

• Copy the data of cells B5:B9 into cells C5:C9.
• Next Right-click on any cell from C5 to C9 and select Format Cells.

• Select Custom under the Number section from the Format Cells dialog box.
• Then add this string ‘@‘ inside the Type box and click OK.

• As a result, you can see that cell C5 is confined to single quotes.

How to Add Double Quotes in Excel

To discuss how to add double quotes in Excel, we added a new column named Country Name between the “City Name” and “With Double Quotes” columns. There are five different ways to add double quotes. The most common ways are: using the Ampersand (&) operator, applying custom format from the Format Cells box, or using the CONCATENATE Function.

1. Using CONCATENATE Function

To add double quotes, you can use the CONCATENATE function as well. To do so,

• Insert the following formula in cell D5 and press Enter.
• Then AutoFill the rest to apply the same formula in the cells D6:D9.

2. Combining CONCATENATE and CHAR Functions

You can apply the CONCATENATE and CHAR functions together to add double quotes. Follow the below steps.

• Insert the following formula in cell D5 and press Enter.
• Then use the AutoFill tool to apply the same formula in the cells D6:D9.

3. Combining CHAR Function and Ampersand Operator

You can also combine the CHAR function with the Ampersand(&) operator to add double quotes. Here are the steps you need to follow.

• Insert the following formula in cell D5 and press Enter.
• Then AutoFill the rest to apply the same formula in the cells D6:D9.

4. Using Ampersand Operator

Using the Ampersand(&) operator you can add double quotes in Excel. Here are the necessary steps.

• Insert the following formula in cell D5 and press Enter.
• Then use the AutoFill tool to apply the same formula in the cells D6:D9.

5. Applying Custom Format Cells Feature

To add double quotes, you can apply a custom format from the Format Cells dialog box. To do so,

• Select the cells B5:C9 and go to the Home tab.
• Then go to Number Format > More Number Formats.

• Select Custom under the Number section from the Format Cells dialog box.
• Then add this string “@” inside the Type box and click OK.

• As a result, you can see that cells B5:C9 are confined to double quotes.

How to Add Quotes for Different Types of Data in Excel (Text, Number, Date and Currency)

So far, we have shown how to add single and double quotes around text values. We will now explain how to add quotes to any type of data (multiple text, Number, Date, and Currency).

For your clear understanding, we have modified our dataset where 6 rows and 5 columns are there.

Now, we will use custom number formatting to add quotes to these different types of data. First, we will add quotes to cell range B5:B9. For that,

• Select cell B5:B9 and press Ctrl+1 to open the Format Cells dialog box.
• Then under the Category list select Custom.
• Next inside the Type text box, enter \”@\” and click OK.

• As a result, double quotes will be added to cell range B5:B9.

Similarly, you can add quotes around multiple text values (C5:C9). To do so,

• Inside the Type text box, enter \”@\” and click OK.
• As a result, double quotes will be added to cell range C5:C9.

Next, we will add quotes to cell D5:D9 which is the salesperson’s ID no.

• Inside the Type text box, enter \”0\” and click OK.
• As a result, double quotes will be added to cell range D5:D9.

Next, we will add quotes around Joining Date and Salary. To add quotes around the Date,

• After opening the Format Cells dialog box, type \”d-mmm-yy\” in the Type box under the Custom category.
• As a result, double quotes will be added to cell range E5:E9.

And, lastly, we will add quotes around Salary which is in Currency format. For that,

• Inside the Type text box, enter \”$#,##0_)\”;[Red]\”($#,##0)\” and click OK.
• As a result, double quotes will be added to cell range F5:F9.

So, like this, you can add quotes to any type of data in Excel using the Format Cells feature.

How to Add Quotes Around Numbers Using Formulas in Excel

In this section, we will show how to add quotes around numbers in Excel with formulas. We will apply a concatenation format for this purpose. We will add quotes to ID No., Joining Date and Salary columns.

To add quotes in ID No. column (simple number format),

• Insert the following formula in cell D12 and AutoFill the rest of the cells of the column.

To add quotes in Joining Date column (date format),

• Insert the following formula in cell E12 and AutoFill the rest of the cells of the column.

And to add quotes in Salary column (currency format),

• Insert the following formula in cell F12 and AutoFill the rest of the cells of the column.

You will get a final output like the below image.

How to Insert Quotes Using Excel VBA

You can use VBA macro to insert quotes easily in Excel. We show VBA code both for inserting single and double quotes.

1. Insert Single Quotes

First, we will add single quotes using Excel VBA. To do so,

• copy the cell range B5:B9 in the cell range C5:C9.
• Select Visual Basic under the Code group from the Developer tab.

• As a result, a new window will appear.
• Next, select Module from the Insert tab.

• Now insert the following code in that module.

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

• Press F5 to run the code and you will get your selected cells with single quotes.

2. Insert Double Quotes

Now, we will add double quotes as well using Excel VBA. To do so,

• Insert a new module and put the following code in that new module.

Sub AddDoubleQuotes()
Dim myCl As Range
For Each myCl In Selection
If myCl.Value <> "" Then
myCl.Value = Chr(34) & myCl.Value & Chr(34)
End If
Next myCl
End Sub

• Press F5 to run the code and you will get your selected cells with double quotes.

How to Remove Quotes from Cells in Excel

Now, we will discuss a simple way of removing quotes from cells in Excel. We will use the SUBSTITUTE function to remove quotes from cells using formulas. Here are the required steps:

• Select cell B12 and enter the following formula.

• Apply the same formula to the remaining cells by AutoFilling downwards and right.
• As a result, all the quotes will be removed from Excel cells.

What Happens When We Directly Input Quotes in Excel Formula Bar?

Let’s say we want to type the text in quotes directly in the Excel Formula Bar.

= “Good Morning!”

The result, however, does not have double quotes.

So, if we need to print the line, we should put double quotes around it. If we directly use the double quotes in the sentence like below:

=” Good Morning “EVERYONE” in the Classroom.”

This will cause an error like the below image.

So it is not possible to type the text in quotes directly in the Excel Formula Bar.

What Is the Limitation of Quotes in Excel?

One common problem is mistyping the formula or format syntax, which can result in unexpected errors. Verify that the syntax is correct and that there are no typographical errors.

What Are the Things You Need to Remember?

Here are a few things you need to remember while working with quotes in Excel.

• You can create a macro or function to automate the process of adding quotes if you do this frequently.
• In your formulas, use relative or absolute cell references.
• Change the format of the quoted text to get the desired output.

Conclusion

This article provides a complete overview of different aspects of quotes in Excel. As well as adding quotes around text, we discussed how to add quotes around other data types. We also demonstrated how to insert quotes using VBA code. Some limitations and tips and tricks were provided while working with quotes in Excel. Hope this article was informative and useful for you. Feel free to leave any questions, comments, or recommendations in the comments section.

Frequently Asked Questions

1. How do I prevent Excel from automatically converting numbers with leading zeros to dates?

Answer: To prevent Excel from converting numbers with leading zeros to dates, you can either format the cells as Text before entering the data or precede the number with a single quote (‘). For example, ‘00123 will be treated as text with leading zeros.

2. What is a smart quote?

Answer: When you type text, Word automatically converts straight quotation marks (‘ or “) into curly quotation marks (also called “smart quotes” or typographer’s quotes).

3. Why is Excel adding quotes to CSV?

Answer: This is completely normal. In this case, the outer quotes are used because it’s a string. To escape it, the inner quote is doubled. This is similar to what you’d see in a SQL query.

Quotes in Excel: Knowledge Hub

• Add Single Quotes
• Add Single Quotes for Numbers
• Add Single Quotes and Comma in Formula
• Concatenate Single Quotes
• Add Double Quotes in Excel Concatenate
• Add Double Quotes and Comma with CONCATENATE
• Remove Hidden Double Quotes

<< Go Back to Concatenate Excel | Learn Excel