How to remove the apostrophe from a string in VBA

Question

I am trying to remove the apostrophe that excel place automatically at the beginning of a string, I wrote the code here below to delete the apostrophe. The code detects the char (') but did not delete it. When you delete it and the string has more than 15 chars, Excel converts it to a scientific notation. The following data is an example of the data I have on the cell in Excel:

'2545879535879652'
'2545879535879652'

Thank.

Private Sub selectRange_Click()
    
    Dim sht As Worksheet
    Dim LastRow As Long
    Dim rGn As Range
    Dim arraySize() As String
    
        
    Set ws = ThisWorkbook.Sheets("PruebaRossi")
    ws.Activate
    LastRow = ws.Range("E" & ws.Rows.Count).End(xlUp).row
   
    Set rGn = ws.Range("E5:E" & LastRow)
      
    rGn.Select
   
   
    Dim cellValue As String
    

    For Each cell In rGn
    
         If Len(cell.value) > 0 Then ' Ensure the cell is not empty
         
            MsgBox (Right (cell.value, 1))

            If Right (cell.value, 1) = "'" Then
            
                MsgBox (Right (cell.value, 1))

                valueCell = Right (cell.value, Len(cell.value) - 1)
                         
                cell.value = valueCell
               

            Else
            
                MsgBox "No, it is not."
                
            End If

        End If

    Next cell
    
End Sub

Answer 1

To remove the leading apostrophe (text prefix), clear the formatting from the cell.

However, if you subsequently select the cell, unless the cell is formatted as text, numbers with more than 15 digits will display in scientific notations. So it is a good idea to also set the formatting of that cell to text:

Option Explicit
Sub RemoveForceTextPrefix()
    Dim rg As Range

Set rg = ThisWorkbook.Worksheets("Sheet1").Range("A.:.A")

With rg
    .ClearFormats
    .NumberFormat = "@"
End With

End Sub

Answer 2

Handle 'Apostrophed' Numbers

• To prevent scientific format, you need to format the cell as a number.
• When entering a value starting with an apostrophe, the apostrophe tells Excel that what follows is text. It is ignored when reading the value, e.g., in cell A1 use '1 and in B1 use the formula =A1. The result in B1 is 1 as text which you can prove with =ISNUMBER("B1") in cell C1 returning FALSE.
• Your code was checking for the right (trailing) apostrophe instead of the left (leading) one, which it wouldn't find anyway thus removing the first digit. If the former was your intent, then you should have used ... = Left(cell.value, Len(cell.Value) - 1).
• Your code has suffered two variable-renaming accidents: sht vs ws and cellValue vs valueCell. To avoid this, use Option Explicit at the top of each module.

Option Explicit

Private Sub selectRange_Click()
     
    Const REMOVE_APOSTROPHES As Boolean = True ' not the leading one
    Const AVOID_SCIENTIFIC_FORMAT As Boolean = True
    ' When set to 'True', converts cells with empty strings to empty cells.
    Const REMOVE_EMPTY_STRINGS As Boolean = False
     
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("PruebaRossi")
    Dim LastRow As Long: LastRow = ws.Range("E" & ws.Rows.Count).End(xlUp).Row
    Dim rg As Range: Set rg = ws.Range("E5:E" & LastRow)
    
    Dim cell As Range, CellValue As Variant, HasToWrite As Boolean
    
    For Each cell In rg.Cells
        CellValue = cell.Value
        If VarType(CellValue) = vbString Then
            If REMOVE_APOSTROPHES Then CellValue = Replace(CellValue, "'", "")
            HasToWrite = True
            cell.ClearFormats
            If IsNumeric(CellValue) Then
                If AVOID_SCIENTIFIC_FORMAT Then cell.NumberFormat = 0
            Else
                If Len(CellValue) = 0 Then
                    If Not REMOVE_EMPTY_STRINGS Then HasToWrite = False
                End If
            End If
            If HasToWrite Then cell.Value = CellValue
        End If
    Next cell

End Sub