Remove leading apostrophe from numbers in Excel - 4 methods

In Excel, numbers formatted as text are often preceded by a leading apostrophe, which can interfere with data processing and calculations. This common issue arises from importing data from other sources or from misformatted user input. Removing these leading apostrophes is essential for accurate data analysis and manipulation. This guide will explore several methods to remove leading apostrophes from numbers in Excel, including using the built-in Paste as Values feature, Kutools for Excel, the Text to Columns feature, and VBA code, providing you with a variety of tools to ensure your data is correctly formatted.

Remove leading apostrophe from numbers in Excel

• By using the built-in Paste as Values
• By using Kutools for Excel easily
• By using the Text to Column feature
• By Using VBA code

Remove leading apostrophe from numbers in Excel

Throughout this guide, we'll explore 4 reliable methods to cleanse your data of unwanted apostrophes, enhancing both its accuracy and usability.

Remove leading apostrophe from numbers with paste as values

The Paste as Values function in Excel is a straightforward method for removing leading apostrophes from numbers. Please do as follows:

1. Select the data range that you want to remove the leading apostrophe, and press Ctrl + C to copy them.

2. Then click one cell where you want to put the result, and right click, then choose 123 value from the Paste Options, see screenshot:

3. And you can see the leading apostrophe has been removed from the numbers.

Remove leading apostrophe from numbers with Kutools for Excel

Kutools for Excel's Convert between Text and Number utility simplifies the process, allowing you to clean multiple cells quickly without the need for repetitive manual adjustments, making it ideal for handling large datasets efficiently.

After installing Kutools for Excel, follow the steps below to learn how to apply this Convert between Text and Numbers feature.

1. Select the range of cells you want to remove apostrophe from numbers, go to select Kutools > Content > Convert between Text and Number.

2. In the Convert between Text and Number dialog box, select the Text to number option, and then click OK.

Result

The apostrophe symbols are now removed from selected numbers immediately.

Download and free trial Kutools for Excel Now !

Remove leading apostrophe from numbers with Text to Column

In Excel, the Text to Column function also can help you to solve this task.

1. Select the data range that you want to remove the leading apostrophe.

2. Then click Data > Text to Column, and in the Convert Text to Columns Wizard, click Finish button directly, see screenshot:

3. And now, you will find all the leading apostrophes have been removed from the numbers.

Remove leading apostrophe from numbers with VBA code

For those who prefer a more automated approach, using VBA (Visual Basic for Applications) code to remove leading apostrophes can offer a customizable and scalable solution. Writing a simple macro can automate the process of stripping out leading apostrophes from numbers, saving time and reducing the potential for human error in large or repetitive datasets.

1. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Remove leading apostrophe from numbers

Sub remove_Apostrophe()
'Updateby20150521
Dim rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeConstants, xlNumbers)
For Each rng In WorkRng
    If Not rng.HasFormula Then
        rng.Formula = rng.Value
    End If
Next
End Sub

3. Then press F5 key to run this code, and in the popped out prompt box, select the data range that you want to use, see screenshot:

4. And then click OK, all the leading apostrophes have been removed from the selected cells.

Effectively removing leading apostrophes from numbers in Excel ensures that your data is not only correct but also usable for calculations and analysis. Each method provided in the post offers unique advantages to suit different needs. By incorporating these techniques into your Excel toolkit, you can streamline your data preparation processes and enhance the reliability of your results, making your Excel workflows more robust and error-free. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Discover more Excel tips and tricks here.