Find/delete single quote character

[phil_a]

Anyone know how to search for a Single Quote (') character in a cell? This is the character excel uses to indicate text formatting, and somehow I have a lot of cells with just ' in them that I need to remove. A Control-Arrow search for the start/end of a contiguous range finds them but find and replace doesn't see them. =code() doesn't see them either.

Allso 'goto blanks' recognises that these cells are non-blank.

Thanks

Phil

 

[texasalynn]

The asap utility has a function that will do this

www.asap-utilities.com

 

[texasalynn]

also found this

Sub RemoveApostrophe()

    For Each CurrentCell In Selection
        If CurrentCell.HasFormula = False Then

            'Checks to make sure that procedure does not
            'change cell with a formula to be only the value
            CurrentCell.Formula = CurrentCell.Value
        End If
    Next

End Sub

 

[BarCodeWiz]

You can check for it using the Range.PrefixCharacter property, like this:

If Cells(currentRow, currentColumn).PrefixCharacter = "'" Then
'Has the prefix
End If

 

[Steve=True]

for a non-VBA helper column:

=CLEAN(SUBSTITUTE(A1,CHAR(39),""))