Find/Replace Unshown Apostrophe

[Guest]

I can't find a similar question to this... I am trying to find a way to take
a cell that shows 0 in the cell but in the formula bar, it shows as '0. I
want to be able to strip out the apostrophe so that Excel will recognize the
0 as a number.

I tried the code below but to no avail.

Sub Find_Replace()
Range("AO6:AQ500").Select
Selection.Replace What:=Chr(39), _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Is it possible to do what I want to do?

 

[Guest]

Select the cells in question and run:

Sub quote_killer()
For Each r In Selection
If r.PrefixCharacter = "'" Then
r.Value = r.Value
End If
Next
End Sub

 

[Guest]

Thanks, Gary's Student - that helped me achieve the desired effect.