Reduce the string and keep zeros

Question

I work with cell values ​​in the format "XXXX-000-000-000".

• All it takes to remove the first hyphen is what I can do.
• The rest of the line should be reduced to integers, with hyphens and any extra zeros.

I have problems with zeros in the right places.

• AD12-002-020-34 You need to look like this: 2-20-34
• CA1-002-101-001 It is necessary that it looks like this: 2-101-1
• AD12-002-020-10 You need to look like this: 2-20-10

For instance:

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)

What gives me: 002-020-100

Suggestions? Thank you in advance!

Answer 1

In addition, it will work as UDF (custom function)

Function STRIP(r As String)
    If InStr(1, r, "-00", vbTextCompare) Then
        r = Replace(r, "-00", "-")
    End If
    If InStr(1, r, "-0", vbTextCompare) Then
        r = Replace(r, "-0", "-")
    End If
    Dim v As Variant, s As String, i As Long
    v = Split(r, "-")
    For i = 1 To UBound(v)
        s = s & "-" & v(i)
    Next i
    STRIP = Right(s, Len(s) - 1)
End Function

=STRIP(A1), A1 ,

:

Answer 2

:

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

Answer 3

UDF (, , - !):

=LEFT(MID(A1,FIND("-",A1)+1,LEN(A1)),FIND("-",MID(A1,FIND("-",A1)+1,LEN(A1)))-1)*1&"-"&VALUE(MID(MID(A1,FIND("-",A1)+1,LEN(A1)),FIND("-",MID(A1,FIND("-",A1)+1,LEN(A1)))+1,FIND("-",MID(A1,FIND("-",A1)+1,LEN(A1)),FIND("-",MID(A1,FIND("-",A1)+1,LEN(A1)))+1)-FIND("-",MID(A1,FIND("-",A1)+1,LEN(A1)))-1))&"-"&VALUE(MID(MID(A1,FIND("-",A1)+1,LEN(A1)),FIND("-",MID(A1,FIND("-",A1)+1,LEN(A1)),FIND("-",MID(A1,FIND("-",A1)+1,LEN(A1)))+1)+1,LEN(A1)))

Answer 4

A regex

Sub TestCleanNames()
   Debug.Print CleanString("AD12-002-020-34")
   Debug.Print CleanString("CA1-002-101-001")
   Debug.Print CleanString("AD12-002-020-10")
End Sub

Function CleanString(strIn As String) As String
    Dim objRegex As Object
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
      'remove first portion
      .Pattern = "(.+?)-"
      CleanString = .Replace(strIn, vbNullString)
      .Global = True
      'trim leasfing zeroes
      .Pattern = "(\-|^)(0)+([1-9])"
      CleanString = .Replace(CleanString, "$1$3")
    End With
End Function

Answer 5

?:

A

Debug.Print Mid(Replace(Replace(Replace(Mid(sString, InStr(1, sString, "-")), "-000", "-"), "-00", "-"), "-0", "-"), 2)

sString - "XXXX-000-000-000"

Answer 6

Sub x()    
    'Technically, you don't have to declare variables, but it prevents typos:
    Dim ir As String
    Dim ir1 As String
    Dim ir2 As String
    Dim ir3 As String
    ir = "AD12-002-020-100"

    'First, get rid of the first 5 characters:
    ir = Right(ir, Len(ir) - 5)

    'Isolate each section. Convert to Int to get rid of leading zeros:
    ir1 = CInt(Left(ir, 3))
    ir2 = CInt(Right(Left(ir, 7), 3))
    ir3 = CInt(Right(ir, 3))

    'Return the result:
    MsgBox ir1 & "-" & ir2 & "-" & ir3
End Sub

Answer 7

, . , A, A1.

:

Sub tgr()

    Dim arrResults() As String
    Dim varText As Variant
    Dim varPart As Variant
    Dim ResultIndex As Long

    With Range("A1", Cells(Rows.Count, "A").End(xlUp))
        ReDim arrResults(1 To .Rows.Count, 1 To 1)
        For Each varText In .Value
            ResultIndex = ResultIndex + 1
            For Each varPart In Split(Mid(varText, InStr(varText, "-") + 1), "-")
                arrResults(ResultIndex, 1) = arrResults(ResultIndex, 1) & "-" & Val(varPart)
            Next varPart
            arrResults(ResultIndex, 1) = Mid(arrResults(ResultIndex, 1), 2)
        Next varText
        .Value = arrResults
    End With

End Sub

UDF:

Function tgrUDF(sText As String) As String

    Dim varPart As Variant

    For Each varPart In Split(Mid(sText, InStr(sText, "-") + 1), "-")
        tgrUDF = tgrUDF & "-" & Val(varPart)
    Next varPart

    tgrUDF = Mid(tgrUDF, 2)

End Function

:

= - MID (SUBSTITUTE (A1, "-", REPT ("", 99)), 99.99) & "-" & - MID (SUBSTITUTE (A1, "-", REPT ("", 99)), 99 * 2.99) & "-" & - MID (REPLACEMENT (A1, "-", REPT ("", 99)), 99 * 3.99)