Reduce string and keep zeroes intact

calendar_today Asked Sep 26, 2013
thumb_up 13 upvotes
history Updated April 16, 2026

Question posted 2013 ยท +4 upvotes

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!

Accepted answer +13 upvotes

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

Top excel-vba Q&A (6)

+13 upvotes ranks this answer #39 out of 136 excel-vba solutions on this site .
excel-vba