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!
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.
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.
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.
Step 3: That’s it. The leading apostrophe would now be successfully removed.
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.
Step 2: If you now look at the cell, you can see that the leading apostrophe is long gone.
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.
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.
Step 3: That’s it. It’s as simple as that. The leading apostrophes are now successfully removed.
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.
Step 2: On the left pane, click on Options as shown below.
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.
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.
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.
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
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.
Step 8: Click on the remove_Apostrophe macro and then hit the Run button on the right.
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.
Step 10: That’s it. The VBA script would automatically remove the leading apostrophes from all the selected cells now. Enjoy!
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.