How to add quotes around numbers or text in Excel?

When working with Excel, it's common to encounter situations where you need to add quotation marks around numbers or text. For example, this is often necessary when preparing data for import into other systems that require quoted values, when generating CSV files for databases, or when creating specialized data formats for specific applications. Manually adding quotes can be time-consuming and prone to errors, especially when dealing with large volumes of data. Fortunately, Excel provides several practical methods to automate this task, each suitable for different needs and scenarios. In the following guide, you'll find a detailed walkthrough of four effective techniques for adding quotes around your data: using the Format Cells function, Kutools for Excel, formulas, and a VBA macro. Each method allows you to add quotes quickly and efficiently, with explanations of their ideal use cases, practical tips, and troubleshooting advice.

Add quotes around texts only with Format Cells function

The "Format Cells" function in Excel provides a simple method for visually adding quotation marks around text values, without changing the underlying cell content. This approach is particularly beneficial when preparing data for printing, reporting, or exporting, where you want to display quotes for clarity, but still retain the original data for calculations or further processing. Please note that numbers converted this way will become text, which may affect formulas or calculations that depend on numeric data types.

1. Select the range of cells containing your text data. Right-click on the selection to open the context menu, then choose "Format Cells".

2. In the "Format Cells" dialog box, go to the "Number" tab. Select "Custom" in the "Category" list and enter the following format in the "Type" field:

"''"@"''"

3. Click "OK" to apply your changes. The text values in your selected range will now appear with quotes around them.

This approach is best suited for small-scale adjustments or when formatting is needed for visual display or export only. For mass data editing, try one of the methods below.

Add quotes around texts or numbers with Kutools for Excel

If you frequently need to add quotes around both text and numbers in Excel or deal with large datasets, Kutools for Excel is a practical solution. Using its "Add Text" feature, you can quickly add quotation marks around cell contents—regardless of whether they are numbers or text—in just a few clicks. This method is highly suitable for users who manage complex data formats, need to reformat imported files, or frequently export data for other systems or programming use. Unlike the "Format Cells" approach, Kutools updates the actual cell content, making the quotes part of the data, which is helpful when exporting or copying data to other applications.

After installing Kutools for Excel, open your Excel file and do the following:

1. Select the entire range of cells in which you want quotes added around the existing text or numbers.
2. From the ribbon, click the "Kutools" tab, then go to "Text" > "Add Text" to open the dialog box.
3. Enter a double quote " into the "Text" input field.
4. Under "Only add to", select "Around text" from the drop-down to ensure quotes are positioned around each cell value.
5. Click "OK" to apply the changes.

   

Result

After clicking OK, you will see that each value in your selected cells—both text and numbers—appears with quotes at the beginning and end, as demonstrated below.

This solution is highly efficient for bulk editing, especially for users who often manipulate data formats or prepare content for programming and data migration tasks. Kutools supports multiple Excel versions, but ensure your add-in is up to date for the best experience.

Add quotes around texts or numbers with formulas

Excel formulas provide a flexible and dynamic way to add quotes to text or numbers. This technique is especially valuable if you want to keep original data unchanged, perform data transformation automatically, or prepare results in adjacent cells for export. It's a good fit when you require quick, repeatable operations and appreciate having results automatically update along with source data changes.

To use this method, follow these steps:

Select a blank cell next to the cell you want to add quotes to (for example, adjacent to A1 in cell B1), and enter a formula such as one of the following depending on your version of Excel:

=""""&J1&""""

=char(34)&J1&char(34)

After inputting the formula, press Enter. To apply the formula to a column or range, click on the fill handle in the bottom-right corner of the cell and drag it down to fill additional rows as needed.

If your data contains quotes or special characters, please review the formula results to ensure correct handling and no double quoting occurs.

Use VBA code to add quotes around cell contents (Batch Add Quotes)

For users needing to process very large datasets, or wishing to automate the quoting process for repetitive tasks, using a VBA macro offers an efficient alternative. VBA allows you to automatically add quotes around all selected cell values—regardless of data type—with a single procedure. This method is particularly suitable for advanced users, people working with massive or dynamically changing spreadsheets, or anyone who wants to integrate this step into a broader workflow or automation routine.

Before using VBA, remember to save your workbook as a macro-enabled file format (*.xlsm), as regular Excel workbooks (*.xlsx) do not support stored macros. Please be cautious with macros received from untrusted sources and always backup your data before running batch processes.

1. Go to the Developer tab, then click Visual Basic to open the Visual Basic for Applications editor. In the new window, click Insert > Module, and paste the following code into the module area:

Sub AddQuotesAroundCells()
    Dim Cell As Range
    Dim WorkRng As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select the range to add quotes:", xTitleId, WorkRng.Address, Type:=8)
    For Each Cell In WorkRng
        If Not IsEmpty(Cell.Value) Then
            Cell.Value = Chr(34) & Cell.Value & Chr(34)
        End If
    Next
End Sub

2. To run the macro, click the (Run) button, or press F5. A dialog box appears prompting you to select the cell range to process. Choose your desired range and confirm.

The macro will automatically add quotation marks to both text and numeric values within the selected range. If any cell is already quoted, running the macro again will add an extra set of quotes, so double-check results if you repeat the action.

This solution is favored for its batch-processing capability, automation potential, and suitability for power users or repetitive quoting tasks across large Excel workbooks.

Demo: Add Text using Kutools for Excel