Macro to to clear leading apostrophes, but not shading?

[slack7639]

I have a Worksheet with 7,000 rows, some of which have shading.

In one of the columns, I started the last 4-digits of a credit card number with a hyphen.

Over time, I have come to see that if you start the numbers with a hyphen, it requires you to have the hidden, leading apostrophe, which is annoying.

I have been trying to simplify things . . . I got rid of the hyphens with this macro:

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Sub Del_Hyphens()

' https://stackoverflow.com/questions/25530193/excel-find-and-replace-macro-one-column-only

Columns("A").Replace What:="-", _
Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

. . . but how do you get rid of the leading apostrophes? . . . here's one way:

Excel 2010 / Home / Editing / Clear - Clear Formats

This isn't bad. Then I just have to change the font back (and maybe the size? I don't think I had to)

. . . but, this also gets rid of the shading, that occurs variously, throughout the 7,000 rows.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

I have already just fixed it manually, but, isn't there something I could have done with a macro, like I did for the hyphen?

For example, whatever is behind what the "Clear Formats" does, is there a breakdown like for the hyphen macro, where I could de-select for it to clear shading?

 

[alansidman]

This worked for me.

Sub Del_Appostrophe()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        Range("A" & i) = Right(Range("A" & i), 4)
    Next i
End Sub

 

[slack7639]

That left the shading, but removed some text I wouldn't want removed.

The problem comes if you're trying to show something like the last 4 of a credit card number - in that it could have a leading zero(s), or you might want to show it as being preceded by a hyphen.

With the cell formatted as General or Number, with:
1.) a leading zero(s)
2.) preceded by a hyphen
. . . a leading apostrophe is required . . . (but if formatted as Number, a Custom Number format could be used)

But if the cell is formatted as Text, no leading apostrophe is required with either.

In my case, I think the easiest thing is to just change that particular column to Text.