减少字符串长度但保留零的数量不变

Question

我正在处理格式为"XXXX-000-000-000"的单元格值。

• 需要删除第一个连字符前面的所有内容，这点我已经做到了。
• 字符串的其余部分需要被转换为整数，并去除连字符和任何多余的零。

我在保留正确的零位方面遇到了麻烦。

• AD12-002-020-34应该看起来像这样: 2-20-34
• CA1-002-101-001应该看起来像这样: 2-101-1
• AD12-002-020-10应该看起来像这样: 2-20-10

例如：

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)

这使我得到：002-020-100。

有什么建议吗？提前感谢！

- Mike

---

你能告诉我如果我们有像这样的字符串“AD12-000-020-100”或“AD12-000-000-100”或“AD12-000-000-000”或“AD12-000-020-000”或“AD12-123-000-000”，输出应该是什么？ - Siddharth Rout

Answer 1

考虑：

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

- Gary's Student

---

+1 鼓励你的工作，你的方法和我的类似，但你的可能更好 :) - user2140173

Answer 2

此外，这将作为用户定义函数（UDF）运行。

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 是您要拆分的任何单元格的引用。

例如：

- user2140173

Answer 3

并不是必须要使用自定义函数（但显然这是更好的选择！）：

=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)))

该公式不是必须要使用自定义函数，但若使用则效果会更好。

- pnuts

---

我对公式一窍不通，看到你的公式就让我毛骨悚然:P 你是自己想出来的还是用了某种生成器？ - user2140173

另一种公式解决方案：=--MID(SUBSTITUTE(A1,"-",REPT(" ",99)),99,99)&"-"&--MID(SUBSTITUTE(A1,"-",REPT(" ",99)),99*2,99)&"-"&--MID(SUBSTITUTE(A1,"-",REPT(" ",99)),99*3,99) - tigeravatar

Answer 4

一个 正则表达式 的方法

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

- brettdj

Answer 5

可以让我也玩吗？ :p 一个单行代码 Debug.Print Mid(Replace(Replace(Replace(Mid(sString, InStr(1, sString, "-")), "-000", "-"), "-00", "-"), "-0", "-"), 2) 其中sString是一个有效的"XXXX-000-000-000"字符串

- Siddharth Rout

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

- PowerUser

---

为什么点赞？你的代码在第二个变量CA1-002-101-001中失败。 - user2140173

你可能没有注意到字符串的第一部分长度是不同的，因此导致结果不准确的原因是 (Right(ir, Len(ir) - 5)) 中的 5。 - user2140173

@mehow，是的，你说得对。而且我试图修复它的尝试越来越像Gary's Student的代码的微小变化。在这一点上，我会向更简洁的编码者低头 :) - PowerUser

Answer 7

我想为这个问题投入我的帮助，注意所有的版本都假设需要转换的单元格位于第一列A单元格开始。

作为宏代码：

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(SUBSTITUTE(A1,"-",REPT(" ",99)),99*3,99)

- tigeravatar