Reduce string and keep zeroes intact

Question

I am working with cell values in a format of "XXXX-000-000-000".

• Everything before the first hyphen needs to be removed, which I can do.
• The rest of the string needs to be reduced to whole numbers, with the hyphens, and any extra zeroes removed.

I'm having trouble keeping the zeroes in the right places.

• AD12-002-020-34 Needs to look like this: 2-20-34
• CA1-002-101-001 Needs to look like this: 2-101-1
• AD12-002-020-10 Needs to look like this: 2-20-10

For example:

dim ir as range

ir = "AD12-002-020-100"

ir1 = InStr(ir, "-")
ir2 = InStrRev(ir, "-")
ir.Offset(0, 1) = Mid(ir, ir1 + 1, ir2 - ir1 + 3)

Which gives me: 002-020-100

Suggestions? Thank you in advance!

Answer 1

Consider:

Sub dural()
    Dim s As String
    s = "AD12-002-020-34"
    s = Replace(s, "-0", "-")
    s = Replace(s, "-0", "-")
    ary = Split(s, "-")
    ary(0) = ""
    s = Mid(Join(ary, "-"), 2)
    MsgBox s
End Sub