Removing trailing zero from values in column?

[XcelNoobster]

So I have column b that are revisions. A revision is either a letter, a number or a dash. But for some reason, some revisions have an extra trailing zero. How would I loop through each value in column b and remove the zero if the character before it is a letter, a number or a dash?

 

[Anthony47]

How revision level 10 (the one after 9) would be normally represented in that cell?

 

[XcelNoobster]

How revision level 10 (the one after 9) would be normally represented in that cell?

Revision will never have two digits or a second digits being 0

 

[Anthony47]

Try this macro:

Sub NoEndZero()
Dim WArr, mySet As String, wLen As Long, I As Long
'
Sheets("Sheet1").Select             '<<< The sheet to be cleaned
'
mySet = "QWERTYUIOPASDFGHJKLZXCVBNMqwertyuiopasdfghjklzxcvbnm12345678990-"
WArr = Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp)).Value
For I = 1 To UBound(WArr)
    wLen = Len(WArr(I, 1))
    If wLen > 1 Then
        If Right(WArr(I, 1), 1) = "0" Then
            If InStr(1, mySet, Mid(WArr(I, 1), wLen - 1, 1), vbTextCompare) > 0 Then
                WArr(I, 1) = Left(WArr(I, 1), wLen - 1)
            End If
        End If
    End If
Next I
Range("B1").Resize(UBound(WArr), 1).Value = WArr
End Sub

Copy it into a standard module of your vba project, edit the line marked <<<, then execute the macro after having created TWO backup copies of your document

 

[kevin9999]

Another option?

Sub Zap_Zeroes()
    With Range("B2", Cells(Rows.Count, "B").End(xlUp))
        .Replace 0, ""
    End With
End Sub