How to Quickly Remove Leading Apostrophe from Numbers in MS Excel

In front of text values, to format the text values as such, often leading apostrophes are used. Since this is just a formatting option, if you try to remove the leading apostrophes using the Find and Replace tool, it would not work. But you definitely could have requirements where you need to remove the leading apostrophes from Excel cells. So is there a way how you can accomplish this task? Well, in this article, we have detailed 4 different ways, not just one!


ADVERTISEMENT

Read on, to learn how you can quickly and easily remove leading apostrophes from numbers in your Excel sheet. Hope you find the article helpful.

 

1 Initial Min

 

Solution 1: By Using Paste Special Options

Step 1: Click on the cell that has a leading apostrophe and then press the CTRL + C keys simultaneously to copy the cell contents.

 

2 Copy Min

 

Step 2: Now click on the result column and then press the keys CTRL + V together to paste what we copied in the previous step.

As next, click on the small Ctrl button that appears when you paste, and then under the Paste Values section, click on the second option.

 

3 Paste Min

 

Step 3: That’s it. The leading apostrophe would now be successfully removed.


ADVERTISEMENT


 

4 Removed Min

 


ADVERTISEMENT

Solution 2: By Using Convert to Number Functionality

Step 1: Click on the cell with a leading apostrophe. Now on the right side to it, you will be able to see a yellow icon. Click on it and then click on the Convert to Number option.

 

7 Convert To Number Min

 

Step 2: If you now look at the cell, you can see that the leading apostrophe is long gone.

 

8 Removed Min

 

Solution 3: By Using Text to Columns Feature

This method is very quick and can be applied to multiple cells in one go.

Step 1: Firstly, select the range of cells that you want the leading apostrophes to be removed from.

Then click on the DATA tab at the top. Under the DATA tab, click on the Text to Columns button.

 

9 Text To Column Min

 

Step 2: When the Convert Text to Columns Wizard opens up, choose the radio button corresponding to Delimited and then hit the Finish button at the bottom.

 

10 Delimited Min

 

Step 3: That’s it. It’s as simple as that. The leading apostrophes are now successfully removed.

 

11 Removed Min

 

Solution 4: By Using a Simple VBA Script

This method involves the use of a simple VBA script. It is very useful in bulk removing leading apostrophes from cells.

Step 1: Firstly we need to enable the Developer tab. If you already have the Developer tab enabled, you can jump to Step 4. Else you can follow the steps below.

To enable the Developer tab, firstly, click on the FILE tab at the top.

 

12 File Min Min

 

Step 2: On the left pane, click on Options as shown below.

 

13 Options Min

 

Step 3: When the Word Options window opens up, on the left window pane, click on the Customize Ribbon option first.

Now on the right pane, under the Customize the Ribbon section, scroll down and check the checkbox corresponding to the option Developer.

Once you are done, click on the OK button.

 

14 Developer Enable Min

 

Step 4: Now that the Developer tab is enabled, you can click on the DEVELOPER tab at the top. As next, click on the Visual Basic button as shown below.

 

15 Visual Basic Min

 

Step 5: When the Visual Basic script editor is open, you need to click on the Insert tab first and then on the Module option.

 

16 Insert Module Min

 

Step 6: As next, on the module, copy and paste the following code.

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

 

 

17 Paste Code Min

 

Once you have copied the code, you can simply close the Visual Basic Editor window. Saving is not mandatory.

Step 7: When you are back at the Excel file, make sure you are at the DEVELOPER tab. Then click on the Macros button.


ADVERTISEMENT

 

18 Macros Min

 

Step 8: Click on the remove_Apostrophe macro and then hit the Run button on the right.

 

19 Run Min

 

Step 9: You will now have a small window named TheGeekPage. Simply select all the cells that you want to remove apostrophes from. This will automatically populate the TheGeekPage window. Hit the OK button once the Range field is populated.

 

20 Select Cells Min

 

Step 10: That’s it. The VBA script would automatically remove the leading apostrophes from all the selected cells now. Enjoy!

 

11 Removed Min

 

Please drop us a comment mentioning your favorite method. Our favorite is the VBA method, as it involves a little bit of scripting.

Please do come back for more amazing tricks, tips, and how-to articles.

 

ADVERTISEMENT