Change the second to last from "0" to "-"

[Steved]

Hello from Steved

Column B:B
numerals 6 to 8

Objective

Change the second to last from "0" to "-" please

I Thankyou.

 

[joel]

Sub replacezero()

MyStr = "abc000"
LastZero = InStrRev(MyStr, "0")
If LastZero > 0 Then
If LastZero > 1 Then
NextZero = InStrRev(MyStr, "0", LastZero - 1)
End If
If NextZero > 0 Then
MyStr = Left(MyStr, NextZero - 1) & "-" & _
Mid(MyStr, NextZero + 1)
End If
End If

End Sub

 

[Steved]

Hello Joel


Joel I'm not able to get your macro to work.

Please what I'm I doing wrong

Steved

 

[Rick Rothstein]

Give this macro a try...

Sub ReplaceNextToLastZeroWithDash()
Dim C As Range, T As String
For Each C In Intersect(Columns("B"), ActiveSheet.UsedRange)
T = C.Text
If Len(T) > 1 Then
If Mid(T, Len(T) - 1, 1) = "0" Then
Mid(T, Len(T) - 1, 1) = "-"
C.Value = T
End If
End If
Next
End Sub

 

[Steved]

Hello Rick

Excellent thankyou

Steved

Give this macro a try...

Sub ReplaceNextToLastZeroWithDash()
Dim C As Range, T As String
For Each C In Intersect(Columns("B"), ActiveSheet.UsedRange)
T = C.Text
If Len(T) > 1 Then
If Mid(T, Len(T) - 1, 1) = "0" Then
Mid(T, Len(T) - 1, 1) = "-"
C.Value = T
End If
End If
Next
End Sub