This post will guide you how to add double quotes around cell values with a formula in your worksheet in Excel. How do I add quotes around each value of cells with VBA Code in Excel 2013/2016. How to add double quotes inside a formula in Excel.

Assuming that you have a long list of names that need to have quotes( it can be double or single quotes) around each name values. If there is a useful formula to add quotes to the name in each row or column.

1. Adding Quotes around Cell Values

If you want to add quotes around text values for the specified cells or enclose all specified cell values with double quotes in range A1:A5, just do the following steps:

Step 1: you can type the following formula based on the CHAR function in Cell B1.

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

or

= " ' " & A1 & " ' "

Step 2: you need to drag the AutoFill handle in Cell B1 down to other cells to apply this formula. Then you would see that all the cell values have been enclosed with double quotes or single quotes.

2. Adding Quotes inside a Fromula

If you want to include double qutotes inside a formula in Excel, and you can use additional double qutoes as escape characters. For example, you wish to add double qutoes around a text, and you can also use the below formula:

= " "" " & A1 & " "" "

3. Add Quotes around Cell Values Using Format Cells Function

If you want to add quotes around cell values in Excel using the Format Cells function, follow these steps:

Step1: Select the cell or range of cells that you want to format.

Step2: Right-click the selected cell(s) and choose “Format Cells” from the context menu.

Step3: In the Format Cells dialog box, go to the “Number” tab. Select “Custom” in the Category list. In the “Type” field, enter the following format code:  “””@”””

This code tells Excel to display the cell value in quotes.

Step4: Click “OK” to apply the format to the selected cell(s). The cell values will now be displayed with quotes around them.

4. Add Quotes around Cell Values with User Defined Function (VBA Code)

You can also create a User Defined Function with VBA code to add quotes around cell values in Excel, just do the following steps:

Step1: Open the Visual Basic Editor by pressing Alt + F11.

Step2: In the Visual Basic Editor, click on “Insert” from the menu bar and select “Module” to create a new module.

Step3: In the new module, enter the following code, save the module with a suitable name.

Function GetCellValue_ExcelHow(rng1 As Range) As String
    GetCellValue_ExcelHow = Chr(34) & rng1.Value & Chr(34)
End Function

Step4: Close the Visual Basic Editor.

Step5: To use the new function, go back to your Excel worksheet and enter the following formula without the quotes into a blank cell.

=QuoteText_ExcelHow(A1)

This will add quotes to the value in cell A1.

The new function, “ QuoteText_ExcelHow “, takes a single argument, “text“, which is the cell value to be quoted. The function returns the text enclosed in quotes using the Chr function to insert the ASCII code for the double quote character (34) before and after the text.

5. Video: Add Quotes around Cell Values

This video will demonstrate how to add quotes around cell values in Excel using the Format Cells feature, VBA code, and a formula.

6. Related Functions

• Excel CHAR function
  The Excel CHAR function returns the character specified by a number (ASCII Value). The syntax of the CHAR function is as below: =CHAR(number)….