Remove Leading Apostrophe From Text & Numbers

[Karen Wamelink]

I have a formula that is pulling both numerical and alpha data from a pivot table with blank cells where no data exists. There are leading apostrophes appended to the left side of the text strings and numbers and in all of the cells that are supposed to be blank; consequently excel does not see the blank cells as blank.

How can I strip the leading apostrophe out with a formula or VBA code?

 

[VoG]

Try this: select the range then run the macro

Sub NoApp()
Dim c As Range
For Each c In Selection
    If Len(c.Value) > 0 Then
        c.Value = Replace(c.Value, "'", vbNullString)
    Else
        c.ClearContents
    End If
Next c
End Sub