How to remove inverted commas from cells in Excel?
When importing data from other applications or external sources into Excel, you may encounter situations where numbers appear with inverted commas (single quotes) at the beginning of the cell values, as illustrated below. This normally happens when Excel interprets the imported content as text rather than numbers. For example, values like '12345 will display with an inverted comma, which can interfere with further calculations, sorting, or formatting. Standard features such as Find and Replace do not remove these characters effectively, especially when they indicate Excel's text formatting. In this article, practical solutions are provided to remove these inverted commas, making your data usable for analysis and computation in Excel.
Remove inverted commas from cells with the Multiply feature
Easily remove all inverted commas from cells with Kutools for Excel
Remove inverted commas using Excel SUBSTITUTE formula
Remove inverted commas using VBA code (macro)
Remove inverted commas from cells with the Multiply feature
Excel includes a handy "Multiply" option within its Paste Special feature, which can efficiently convert numbers stored as text (including those with leading inverted commas) into actual numeric values. By multiplying the affected cells by 1, Excel treats the contents as numbers and removes the inverted commas.
1. Type the number 1 into any empty cell in your worksheet, then copy it by selecting the cell and pressing Ctrl + C.
2. Highlight the range of cells containing inverted commas that you'd like to fix. Next, on the ribbon, click Home > Paste > Paste Special. This opens the Paste Special dialog.
3. In the Paste Special window, under the Operation section, select Multiply and click OK.
Instantly, Excel recalculates the contents and removes the inverted commas from each selected cell.
Note: This method is fast and simple, but the result will overwrite original values in the selected cells. Always make a backup or practice on sample data first when working with important information. It's best suited for cells containing only numeric text—other types of content may require different approaches.
Remove inverted commas from cells with Kutools for Excel
The Remove Characters tool of Kutools for Excel can help you clean up all inverted commas from selected cells in bulk, regardless of whether they're at the beginning, end, or within cell contents. This approach is highly effective if your spreadsheet contains various types of text or code.
1. First, select the cells from which you want to remove inverted commas. Click Kutools > Text > Remove Characters.
2. In the Remove Characters popup, check the Custom box, then type the inverted comma (') in the textbox provided. Click OK to proceed and instantly clean all selected cells.
Now, your spreadsheet is free of inverted commas in the specified area.
Tips: This utility can also remove other unwanted characters in bulk. If your sheet contains both single and double quotation marks or other symbols, simply list them in the Custom textbox.
Precautions: Changes take effect immediately, so it's recommended to select just the affected columns or make a backup prior to mass removal. If you want to preview or undo changes, use the built-in undo feature (Ctrl + Z) in Excel.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Remove inverted commas using Excel SUBSTITUTE formula
For situations where you want to process your data without changing the original cells—such as when building a dynamic report or maintaining raw data—you can use an Excel formula to remove inverted commas on-the-fly. The SUBSTITUTE function is an efficient solution for this scenario, allowing you to automatically remove inverted commas as you work.
1. In a new column (for example, if your original value is in cell A1, use cell B1), enter the following formula:
=SUBSTITUTE(A1,"'","")This formula scans the value in cell A1 and removes all instances of the inverted comma (').
2. Press Enter to apply the formula. To clean additional cells, drag the fill handle down or double-click it to fill the formula for more rows.
Tip: This method doesn't overwrite original data and is especially useful for reporting, charting, or if you want to keep your source data intact. After cleaning, you can copy the results and use Paste Special > Values to overwrite old data if required.
Remove inverted commas using VBA code (macro)
If your workbook contains thousands of cells or complex combinations of text and numbers, running a VBA macro can be an efficient way to batch-remove inverted commas. This technique provides flexibility for working with wide ranges or automating the clean-up process, especially when other features don't catch every case.
1. Go to Developer Tools > Visual Basic. In the Microsoft Visual Basic for Applications window, click Insert > Module and enter the following code:
Sub RemoveInvertedCommas()
Dim rng As Range
Dim cell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the range to clean", xTitleId, rng.Address, Type:=8)
For Each cell In rng
If Not IsEmpty(cell.Value) Then
cell.Value = Replace(cell.Value, "'", "")
End If
Next
End Sub2. To run this macro, click the 
button in the VBA window. A prompt will appear for you to select the range of cells to process. Once you confirm, the macro will clean the selected cells by removing all inverted commas instantly.
Tip: Always save your file before running macros. For extra safety, make a copy of your worksheet or add a backup step to the macro if you have complex data.
Related articles:
- How to remove text before or after a specific character in Excel?
- How to add comma at end of cell/text in Excel?
- How to add text in middle of selected cells in Excel?
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in