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

We will use the following sample dataset to insert quotes in various cells.

Download the Practice Workbook

Use of Quotes.xlsm

What Is the Key Difference Between Single and Double Quotes 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 to Text in Excel

Method 1 – Use the CHAR Function to Add Single Quotes in Excel

Here’s an overview of the char function. CHAR(39) denotes single quotes.

Insert the following formula in cell C5 and press Enter to wrap the text in B5 in single quotes.

=CHAR(39)&B5&CHAR(39)

Use the AutoFill tool to apply the same formula in the cells C6:C9.

Method 2 – Insert Single Quotes with the CONCATENATE Function

Here’s an overview of the CONCATENATE function:

Insert the following formula in cell C5 and press Enter to wrap the value from B5 in quotes.

=CONCATENATE("'",B5,"'")

Use the AutoFill tool to apply the same formula in the cells C6:C9.

Method 3 – Use a Formula with the Ampersand Symbol to Add Single Quotes

Insert the following formula in cell C5 and press Enter.

="'"&B5&"'"

Use the AutoFill tool to apply the same formula in the cells C6:C9.

Method 4 – Apply a Custom Format to Insert Single Quotes

Copy the data of cells B5:B9 into cells C5:C9.
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.
Add ‘@‘ inside the Type box and click OK.

This wraps the contents in single quotes.

How to Add Double Quotes to Text in Excel

We added a new column named Country Name between the “City Name” and “With Double Quotes” columns.

Method 1 – Using CONCATENATE Function

Insert the following formula in cell D5 and press Enter.

=CONCATENATE("""",B5,""""," in ","""",C5,"""")

AutoFill to apply the same formula in the cells D6:D9.

Method 2 – Combining CONCATENATE and CHAR Functions

Insert the following formula in cell D5 and press Enter.

=CONCATENATE(CHAR(34),B5,CHAR(34)," in ",CHAR(34),C5,CHAR(34))

Use the AutoFill tool to apply the same formula in the cells D6:D9.

Method 3 – Combining the CHAR Function and the Ampersand Operator

Insert the following formula in cell D5 and press Enter.

=CHAR(34)&B5&CHAR(34)&" in "&CHAR(34)&C5&CHAR(34)

Use AutoFill to apply the same formula in the cells D6:D9.

Method 4 – Using the Ampersand Operator

Insert the following formula in cell D5 and press Enter.

=""""&B5&""""&" in "&""""&C5&""""

Use the AutoFill tool to apply the same formula in the cells D6:D9.

Method 5 – Applying the Custom Format Cells Feature

Select the cells B5:C9 and go to the Home tab.
Go to the Format drop-down and select More Number Formats.

Select Custom under the Number section from the Format Cells dialog box.
Put “@” inside the Type box and click OK.

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)

We have modified our dataset to include various data types.

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

Note: For adding single quotes, type ‘\@\’ in the Type text box.

Double quotes will be added to the cell range B5:B9.

You can add quotes around multiple text values (C5:C9).
Go to Custom Format, and inside the Type text box, enter \”@\” and click OK.
As a result, double quotes will be added to cell range C5:C9.

For the D5:D9 range, which contains numbers, inside the Type text box, enter \”0\”.

To add quotes around the Date, in the Format Cells dialog box, type \”d-mmm-yy\” in the Type box under the Custom category.

For the Salary, which is in the Currency format, inside the Type text box, enter \”$#,##0_)\”;[Red]\”($#,##0)\” and click OK.

Note: Negative values in the Salary column will be marked as red and be in parentheses.

How to Add Quotes Around Numbers Using Formulas in Excel

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

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

=CHAR(34)&D5&CHAR(34)

To add quotes in the Joining Date column (date format):

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

=CHAR(34)&TEXT(E5, "dd/mm/yy")&CHAR(34)

And to add quotes in the Salary column (currency format):

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

=CHAR(34)&TEXT(F5, "$#,##0")&CHAR(34)

How to Insert Quotes Using Excel VBA

Method 1 – Insert Single Quotes

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

A new window will appear.
Select Module from the Insert tab.

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

Code Breakdown:

Dim myCl As Range declares a variable to represent a cell in a worksheet.
For Each myCl In Selection

If myCl.Value <> “” Then
myCl.Value = Chr(39) & Chr(39) & myCl.Value & Chr(39)
End If

This Loop goes through each cell in the currently selected range (selection). It checks if the value in the current cell is not empty. If the value is not empty, then it adds two single quotes before and one after the value.

Next myCl refers to moving to the next cell in the selection.

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

Method 2 – Insert Double Quotes

Insert a new module and put the following code in it.

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

Code Breakdown:

Dim myCl As Range declares a variable to represent a cell in a worksheet.
For Each myCl In Selection

If myCl.Value <> “” Then
myCl.Value = Chr(34) & Chr(34) & myCl.Value & Chr(34)
End If

This Loop goes through each cell in the currently selected range (selection). It checks if the value in the current cell is not empty. If the value is not empty, then it adds one double quote before and one after the value.

Next myCl refers to moving to the next cell in the selection.

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

How to Remove Quotes from Cells in Excel

Select cell B12 and enter the following formula.

=SUBSTITUTE(B5, """", "")

Apply the same formula to the remaining cells by AutoFilling down and right.
All the quotes will be removed from Excel cells.

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

We want to type the text in quotes directly in the Excel Formula Bar.

= “Good Morning!”

The result, however, does not have double quotes.

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.

What Is the Limitation of Quotes in Excel?

One common problem is mistyping the formula or format syntax, which can result in unexpected errors.

Frequently Asked Questions

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

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.

What is a smart quote?

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

Why is Excel adding quotes to CSV?

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

<< Go Back to Concatenate Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!