Trouble recognizing ' with VBA

[mtterry]

If the contents of a cell start with ', I'm trying to change them using VBA - the first step being to delete the single quotation; however, I'm having trouble getting VBA to recognize the single quotation in the below If statement:

If Left(cel.Value, 1) = "'" Then ..... - I tired cel.text as well and same issue. All of the cells that do start with a single quotation are followed by a 0 - and if I use this:

If Left(cel.Value, 1) = "0" Then...... it will recognize every cell that starts with either a ' or a 0.

I'm not sure how I can get VBA to pick up the ', any help would be appreciated.

 

[Fluff]

Try

If activecell.PrefixCharacter = Chr(39) Then MsgBox "yes"

 

[Eric W]

When a cell has a ' at the start, it's really not part of the value of the cell. It's just an indicator to Excel that the contents of the cell is text. That's why your Left() constructions don't find it. If you actually want a ' as part of the value, you'd have to do something like:

="'This is a test"

Your Left() will pick it up in this case.

 

[mtterry]

This worked great to identify ' - but now I'm struggling to remove it. It appears from the treatment that I likely don't need to remove it now, but I'm curious to know if/how that can be accomplished?

 

[RoryA]

You'd need to clear the formatting of the cell.