How to Remove Leading Apostrophe From Numbers in Excel?


You've come to the right place if you've ever had numerical data in an Excel spreadsheet start with an undesired apostrophe. These apostrophes may occasionally be the result of data imports or from outside sources, and they may interfere with calculations or data formatting.

This step-by-step tutorial will show you how to efficiently and correctly eliminate the leading apostrophes from your data, making sure that it is accurate and prepared for further analysis. This article doesn't assume any prior knowledge of Excel, so both novice and advanced users will find it simple to follow. Let's start now and get rid of those annoying apostrophes!

Remove Leading Apostrophe From Numbers

Here we will first create a VBA module and then select the range of cells to complete the task. So let us see a simple process to learn how you can remove leading apostrophes from numbers in Excel.

Step 1

Consider an Excel sheet where you have cells with apostrophes similar to the below image.

First, right-click on the sheet name and select View code to open the VBA application.

Right-click > View Code.

Step 2

Then click on Insert and select Module, then copy the below code into the text box.

Insert > Module > Copy.

Code

Sub remove_Apostrophe()
Dim rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Remove Leading Apostrophe"
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

Step 3

Then click F5 to run the module. Then select the range of cells and click OK to complete the task. You will see that the leading apostrophe will be removed.

F5 > Select Cells > OK

This is how you can remove leading apostrophes from numbers in Excel.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can remove leading apostrophes from numbers in Excel to highlight a particular set of data.

Updated on: 08-Sep-2023

57 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements