How to Add Quotes Around Text in Excel

- Written by Puneet

excelchamps-free-courses-puneet-gogia

Let’s say you work in a customer service team and need to send a list of customer names to your IT department.

The IT department requires the names to be in a specific format, with each name enclosed in quotes, like “John Doe”.

In Excel, I have found five different formulas you can use to add quotes around the text. You can use these formulas in various situations and your requirements.

Use AMPERSAND (&) to Add Quotes Around Text in Excel

This is the easiest and quickest way to write the formula for adding quotes around the text.

  1. In this formula, you first need to enter four quotes, followed by user ampersand (&).
  2. After that, you need to refer to the cell where you have the text or enter the text within the formula directly.
  3. Now again, enter an ampersand (&) and then enter four quotes again.
  4. In the end, hit enter to get the result value in the cell.
="""" & A1 & """"

You can see in the above example we have a name in cell A1, and with the formula, we got the same name surrounded by the quotes in cell B1.

Why we use Four Quotes for a Single Quote

If you have seen the above formula, you might be asking why we have used four quotes for one side of the quote.

In Excel, four double quotes on one side represent a single double-quote character when writing a formula. Double quotes (“”) denote text strings in Excel formulas. So, to include a double quote character inside a text string, you need to escape it by doubling it up.

When you want to add double quotes around a text value (e.g., around the text in cell A1), you should ensure Excel correctly interprets the double quotes as part of the text, not as the end of the text string. Thus, you use four quotes to represent one actual double-quote character.

Use CHAR & Ampersand (&) to Add Quotation Marks to a Text

In Excel, when you use CHAR(34) it returns the double quote character (“). The number 34 corresponds to the double quote (“) in the ASCII character set. So, when you use CHAR(34) in a formula, it inserts a double quote character.

And when you use a formula like the one below:

=CHAR(34) & A1 & CHAR(34)

When you enter this formula, double quotes around the text “John Doe” in cell A1 are added by placing the character corresponding to 34 (the double quote) before and after the text.

Add Quotes with CONCATENATE

In the same way, you can write a formula using the CONCATENATE function. For this, you can write a formula like the following:

=CONCATENATE("""", A1, """")

This formula adds double quotes around the text in cell A1. It combines (or concatenates) a double quote, the text from A1, and another double quote.

Use TEXTJOIN to Add Quotes

You can also use TEXTJOIN to join text with a delimiter. And you can write a formula like the following:

=TEXTJOIN("", TRUE, """", A1, """")
  • =TEXTJOIN(“”, TRUE, – The first two arguments define that there’s no separator between the values, and it will ignore the empty cells.
  • “”””, A1, “”””) – These are the three values that you want to combine. The first and the third values are quotation marks, and the middle value is text from cell A1.

REPT Function for Quotation Mark

I am a fan of the REPT function, but do not use it to add quotation marks. But it makes sense if you want to use it to add quotation marks.

=REPT("""",1) & A1 & REPT("""",1)
  • REPT(“”””,1) creates a starting quote.
  • & A1 & combines the starting quote, the text in A1, and ending quote.
  • REPT(“”””,1) adds the ending quote.

So, it adds quotes around the text in A1.

Create a Custom Function to Add Quotes Around the Text

If you are required to add quotes around a text frequently, you can also use the VBA code to create a custom function that can add quotes without using any complex formula. You need to refer to the cell where you have the text, and it will return the text with quotes.

=AddQuotes(A1)
Function AddQuotes(text As String) As String
    ' Add quotes around the text
    AddQuotes = """" & text & """"
End Function

Press ALT + F11 to open the VBA editor, and then click Insert > Module to create a new module. Paste the following code into the module. After that, you can use the AddQuotes function in your worksheet.

Use Custom Formatting

Let’s say you don’t want to add quotes to the text value but want to show it to the user. For this, you can use custom formatting instead of a formula.

  1. Select the cells that you want to format with quotes.
  2. Right-click on it and choose “Format Cells,” or press Ctrl + 1 to open the Format Cells option.
  3. In the Format Cells, go to the “Number” tab and select “Custom” from the list on the left.
  4. In the “Type” field, enter the following custom format “@” .
  5. Click OK to apply the apply the formatting.
Last Updated: July 16, 2024

Leave a Comment