How to add quotes around numbers or text in Excel?
When working with Excel, you might encounter situations where you need to add quotes around numbers or text, such as when preparing data for import into other systems or creating specific data formats. Adding quotes manually can be tedious, especially if you have a large dataset. Fortunately, there are several methods available in Excel to streamline this process. This guide will walk you through three effective techniques for adding quotes around your data: using the Format Cells function, Kutools for Excel, and formulas. Each method offers a unique approach to help you achieve the desired formatting quickly and efficiently.
Add quotes around texts only with Format Cells function
The Format Cells function in Excel provides a straightforward way to add quotes around text without altering the underlying data. This method is particularly useful when you need to display text with quotes for presentation purposes or data formatting, without changing the actual content of the cells. Here’s how you can use this function to add quotes around text values:
1. Select the data range, right click to display context menu, select Format Cells.
2. In the Format Cells dialog, under Number tab, select Custom from the Category list, and then type the following format into the Type textbox.
"''"@"''"
3. Click OK. Now only the texts are added quotes around.
Note: The number cells will be changed to text format.
Add quotes around texts or numbers with Kutools for Excel
Kutools for Excel offers a convenient tool – Add Text for adding quotes around both text and numbers in a range of cells with just a few clicks. This method is ideal for users who need a quick and efficient way to format large datasets without manual entry or complex formulas. Here’s how to use Kutools for Excel to add quotes around your data:
After installing Kutools for Excel, go to the Kutools tab and select Text > Add Text to open the Add Text dialog box. Then you need to ocnfigure as follows
- Select the range where you want to add quotes around the texts and numbers.
- Enter a quote in the Text field.
- Select the Only add to option and choose Around text from the drop-down.
- Click OK.
Result
Then quotes are added around text and number in each cell of the selected range as shown in the screenshot below
Add quotes around texts or numbers with formulas
If you prefer to use formulas to add quotes around your data, Excel provides a flexible way to achieve this. This method is useful when you need to include quotes as part of a formula-driven solution or when dealing with specific data transformations. Follow these steps to use formulas for adding quotes:
Select a cell next to the cells you want to add quotes around, then apply one of the following formulas:
=""""&J1&""""=char(34)&J1&char(34)Drag fill handle to fill formula to the cells you need.
Add Text
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
