Small improvement on the (excellent) answer from LocEngineer. Please upvote his answer instead of this one.

Below restores the original cell format before restoring the cell value. That way, cell values with leading zeros (ex: "001") will be restored with their leading zeroes intact.

'Purpose:
'   Removes the (unusual) leading apostrophe that exists in cell values that try to force a "text" format.
'
'Notes:
'   The leading apostrophe character is weird.  One can select it in the formula bar.  However, Excel formulas ignore it.
'
'   This sub only removes the apostrophe if it is a special "PrefixCharacter".  This sub will not affect cells that do NOT have
'   a PrefixCharacter (but do have a leading apostrophe as a legitimate text value) - OK.
'
'   Modified from: https://stackoverflow.com/a/47510941/722945  The solution did remove
'   the prefix "'" but it also changed the cell format from "text" to "general" and removed any leading zeros from cell values.  The
'   updates below resolve this issue.
'
'Example:
'   Call RemoveApostrophePrefixCharacter(ActiveCell)
'Tested: 2024-12-04
Sub RemoveApostrophePrefixCharacter(rng As Range)
    Dim cell_obj As Range
    Dim original_value As Variant
    Dim original_number_format

    For Each cell_obj In rng
        'Safety check is not really needed.  Helps limit the scope to only update cells that have issue (saves a little CPU).
        If cell_obj.HasFormula = False And cell_obj.PrefixCharacter = "'" Then
            'Ex: Will save string "@" if text format.
            original_number_format = cell_obj.NumberFormat
        
            original_value = cell_obj.value

            'Note: This will also clear the formatting.
            cell_obj.Clear
            
            'Restore cell format.  Mainly to restore format if the format is "text".
            'This is required for values with leading zeroes (ex: "001").  I tried calling "clear()", then reset the cell value, but the cell format turned from "text" to "general" and the leading zeroes were removed.
            cell_obj.NumberFormat = original_number_format

            'Restore cell value.
            cell_obj.value = original_value
        End If
    Next


End Sub