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

Hello from Steved

Column B:B
numerals 6 to 8

Objective

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

I Thankyou.

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

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" wrote:

Hello from Steved

Column B:B
numerals 6 to 8

Objective

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

I Thankyou.

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

Hello Joel


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

Please what I'm I doing wrong

Steved

"joel" wrote:

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" wrote:

Hello from Steved

Column B:B
numerals 6 to 8

Objective

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

I Thankyou.

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

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

--
Rick (MVP - Excel)


"Steved" wrote in message
...
Hello from Steved

Column B:B
numerals 6 to 8

Objective

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

I Thankyou.

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

Hello Rick

Excellent thankyou

Steved

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Steved" wrote in message
...
Hello from Steved

Column B:B
numerals 6 to 8

Objective

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

I Thankyou.