Article 27 – Remove Leading Apostrophe

Leading apostrophe is one of the widely used features of Excel. This is mostly used to make a cell treat its content as a character. It can also come through import of .csv files from various third party systems or data entries can be made with leading apostrophes. If you enter a number of more than 15 significant digits, Excel will immediately convert this to scientific notation. But there are business situations where you deal with numbers larger than 15 digits like credit card numbers which may be 16 digits. Now if you input credit card numbers as numeric, you will not be able to capture all 16 digits, so a solution is to enter them with leading apostrophe. It forces the cell to treat the entry as character and in character format you can show these 16 digits.(You can enter 32,767 characters in a cell though only 1024 characters will be displayed)

Now, Apostrophe is really not part of the cell's content, hence you will not be see this in the cell. But if you select the cell, you can see the leading apostrophe in the formula bar.

Since Apostrophe is not part of the cell's content, you can not use Excel's Find and Replace feature to remove leading apostrophes.

Following are the methods to remove Leading Apostrophes in Excel

Manual Methods

Method 1 – Just Copy and Paste as Values. This is the quickest method and works on both Text and Numeric entries. The drawback of this method is that while apostrophe is removed, the entries still are Text.

Method 2 – Home tab > Clear Format – This works on both Text and Numeric entries. The drawback of this method is same as above.

Method 3 – CTRL+C a blank cell > Select the range > Paste Special > Add. This works on both Text and Numeric entries and removes apostrophes. Numbers gets converted to Numbers unlike Methods 1 & 2

Method 4 – This seems to be best method. Just select your range and Data tab > Text to Columns > Keep pressing OK till Finish. This works on both Text and Numeric entries and removes apostrophes. Numbers gets converted to Numbers unlike Methods 1 & 2.

Method 5 – This is another method which may also qualify as best method. Just Save As .csv and close the file and reopen. This works on both Text and Numeric entries and removes apostrophes. Numbers gets converted to Numbers unlike Methods 1 & 2.

Formula Methods

Method 1 – Adding a 0 / subtract a 0 / multiply by 1 / division by 1 – This will remove apostrophe and will convert Numbers into Numbers. The drawback is that this does not work on non-numbers.

Method 2 – CLEAN function will also remove apostrophes. But entries will still be Text.

VBA Method

A sample code is given below which you can customize as per your needs.

Sub RemoveApos()
    Dim Rng As Range, Cell As Range
    Worksheets("Sheet1").Activate
    Set Rng = Cells.SpecialCells(xlConstants)
    Cells(Rows.Count, Columns.Count).Copy
    For Each Cell In Rng
        If Cell.PrefixCharacter = "'" Then
            Cell.PasteSpecial Operation:=xlPasteSpecialOperationAdd
        End If
    Next Cell
    Application.CutCopyMode = False
End Sub