用Excel VBA最高效的方法按分隔符拆分字符串，同时忽略某些分隔符实例

Question

我有一段非常冗长、陈旧的代码，当我第一次发现并决定学习vba时，我写了它（现在我已经进步很大了）。我使用这段代码来循环遍历包含逗号分隔的多个值的单元格。然而，有些情况下我不能简单地使用 Split(string,",") 这样的东西，因为某些值中包含逗号（例如值：[blah blah, so blah blah]）。在存在这些括号的情况下（每个包含逗号的值都有括号），我想出了一个相当复杂的方法来正确地拆分这些值，将它们放入一个数组中，然后继续执行其他任务。但是，现在我决定重新审视代码并修复精度问题。以下是一些背景信息。 可以在单个单元格中找到的示例数据：
^{请注意：这是供应商发送给我们的数据，我们无法控制他们输入或输入方式。这是一个简单的示例，展示了数据通常在某些情况下如何提供}

Available on 2 sides: Silkscreen,[full: color, covers entire face],Pad Print: One color,[heat transfer, may bleed]

数值如下:

• 可在2个方面使用:丝网印刷
• [全色，覆盖整个面部]
• 垫印：单色
• [热转印，可能会出现渗透]

我的需求:
我正在寻找一种更有效和简单的方法来正确地拆分值（同时保留具有括号的值的括号）。

我相信我已经成功创建了一种更有效和紧凑的处理不包括括号的实例的方法，使用以下代码：

新代码（正在建设中）： 我遇到了一个问题，不知道如何高效而准确地拆分带有括号的单元格。

Sub Test()
    Dim rngXid As Range, RegularColons As New Collection, UpchargeColons As New Collection, additionals As Range, upcharges As Range, Colon, UpchargeColon
    Dim Values() As String, endRange As Long, xidMap As Object, xid As String, NumberofValues As Integer
    endRange = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

    Set xidMap = getXidMap(ActiveSheet.Range("A2:A" & UsedRange.Rows.Count)) 'Map products for quicker navigation
    Set additionals = ActiveSheet.Range("AJ:AK"): Set upcharges = ActiveSheet.Range("CS:CT")
    Set RegularColons = FindAllMatches(additionals, ":") 'This returns all instances/cells that contain a colon in the specified columns
    If Not RegularColons Is Nothing Then
        For Each Colon In RegularColons
            xid = ActiveSheet.Range("A" & Colon.Row).Value
            If InStr(1, Colon.Value, "[") = 0 Then 'If no brackets then simply split
                Values = Split(Trim(Colon.Value), ",")
            Else
                'This is where I'm at a lose for a more effective method
                '-----------Populate Values array with Colon.Value while watching out for brackets--------
            End If
            Set rngXid = xidMap(xid).EntireRow.Columns(upcharges) 'set to this specific product
            For ColorLocation = LBound(Values) To UBound(Values) 'cycle through each value in Values array
                If Not InStr(1, Values(ColorLocation), ":") = 0 Then 'Only proceed if the value has a colon
                    Set UpchargeColons = FindAllMatches(rngXid, Values(ColorLocation)) 'Searching other columns for this value
                    If Not UpchargeColons Is Nothing Then
                        For Each UpchargeColon In UpchargeColons 'If found in other columns proceed to replace colon
                            UpchargeColon.Value = Replace(UpchargeColon.Value, ":", " ")
                            Log UpchargeColon.Range, "Removed Colon from Additional Color/Location Upcharge", "Corrected" 'This is a custom sub of mine to record the change
                        Next UpchargeColon
                    End If
                    Values(ColorLocation) = Replace(Values(ColorLocation), ":", " ")
                End If
            Next ColorLocation
            Log Colon.Range, "Removed Colon(s) from Additional Color/Location Value(s)", "Corrected"
        Next Colon
    End If
End Sub

我一直在浏览可能的方法来完成这个任务，其中一个最突出的方法是正则表达式（Regex），虽然我之前听说过，但实际上没有任何经验。因此，我尝试阅读了一些网站（如这里和当然是msdn文档）中关于它的相关知识。我在学习这种方法时的观察/思考如下：

1. 这绝对是非常复杂和压倒性的。它甚至让我不敢想象自己会不会蜷缩在角落里，假装胎位，哭泣不已。
2. 我似乎找不到任何可以将此函数与适当拆分字符串的需求相结合的方法，至少我所看到的。但是，这可能只是因为我被所有看起来都是随意符号序列淹没了。

所以，我的问题是：
在包含括号的单元格中，最有效的准确拆分值的方法是什么？

- CaffeinatedMike

---

就此事，我已将其标记为迁移到CR。 - Mathieu Guindon

@CaffeinatedCoder 实际上，正则表达式的问题不在于它们非常复杂，而在于它们过于简单化，即它们非常“愚蠢”，只能有效解析最简单的文本。 - Phrancis

@Mat'sMug 嗯，是的，但是这个特定子程序的整个代码有300多行，并且非常新手级别。我的新代码基本上完成了，已经在上面列出。我只需要帮助处理最后一段我的新代码（解决带括号的单元格问题）。现在我觉得在这里发帖比CR更合适（抱歉！）。 - CaffeinatedMike

仅供参考，VBA中使用的正则表达式语法与JavaScript、VBScript和其他ECMAScript实现中使用的相同。它与.NET语法不同，后者具有更丰富的功能集。因此，如果您想学习在VBA中使用正则表达式，则您链接的MSDN文档不是开始的地方。 - Alan Moore

这是VBScript正则表达式的信息 - https://msdn.microsoft.com/zh-cn/library/ms974570.aspx - Tim Williams

Answer 1

还有其他方法，但这个正则表达式似乎非常快：

(\[[^\]]+\]|[^,]+),?

解释：

\[ 和 \] 是 [ 和 ] 的转义版本。

基本上，它正在寻找一个 \[，获取所有非括号的字符 [^\]]，然后是 \]。否则，使用 | 获取所有非逗号的字符 [^,]。周围的 () 使其成为一个捕获组。 ,? 表示可能有逗号，也可能没有逗号。

- Laurel

---

这是如何工作的？抱歉，我目前在阅读/理解正则表达式方面非常愚钝。话虽如此，我需要一些能够处理包含和不包含结尾逗号实例的东西（例如：单元格中的最后一个值没有尾随逗号）。 - CaffeinatedMike

这让我对它的工作原理有了稍微更好的了解（谢谢！）。现在，我该如何使用它将单元格中的值拆分成数组？ - CaffeinatedMike

@CaffeinatedCoder 我不懂VBA，请在这里尝试一下。 - Laurel

讲解得很清楚，干得好 - 请注意，您可以省略,?部分，因为您实际上不想在Match中使用逗号。 - Mathieu Guindon

@Mat'sMug 这不在捕获组中。 - Laurel

Answer 2

一种方法是将方括号中的逗号替换为Chr(184)。这些小家伙看起来很像逗号。

一旦方括号中的逗号被替换，您可以使用正常的Split()函数。以下是一些进行替换的代码：

Sub parser()
    Dim s As String, s1 As String, s2 As String, pseudo As String
    Dim switch As Boolean, temp As String, CH As String

    pseudo = Chr(184)
    s1 = "["
    s2 = "]"
    s = [A1]
    switch = False
    temp = ""

    For i = 1 To Len(s)
        CH = Mid(s, i, 1)
        If CH = s1 Or CH = s2 Then switch = Not switch
        If switch Then CH = Replace(CH, ",", pseudo)
        temp = temp & CH
    Next i

    Range("A2").Value = temp
    MsgBox s & vbCrLf & temp
End Sub

- Gary's Student

---

这是一个不错的方法，但如果在成千上万行数据中实现，每一行/单元格可能有超过100个字符，它是否高效呢？我只是在扮演魔鬼的代言人，并试图假设最坏的情况（因为这种情况不幸地发生过）。 - CaffeinatedMike

@CaffeinatedCoder……我喜欢这种方法的原因是我可以预处理数千条记录，然后稍后再处理它们……我甚至可以将其作为数据导入的一部分进行预处理。 - Gary's Student

你可以优化这段代码，先检查所有字符中是否存在 [ 和 ] 再进行解析。 - ThunderFrame

Answer 3

正则表达式（也称为“regex”）看起来确实很吓人，但它们也是一种强大的工具，如果您添加对 Microsoft VBScript Regular Expressions 5.5 库的引用，VBA就支持它们。

使用它，您可以创建一个 RegExp 对象，该对象提供了一个 MatchCollection，即一组 Match 对象。

以下是如何使用它们的方法：

Sub Test()
    Const value As String = _
    "Available on 2 sides: Silkscreen,[full: color, covers entire face],Pad Print: One color,[heat transfer, may bleed]"

    Const pattern As String = _
    "(\[[^\]]+\]|[^,]+)"

    Dim regex As New RegExp
    regex.Global = True
    regex.pattern = pattern

    Dim matches As MatchCollection
    Set matches = regex.Execute(value)

    Dim m As Match
    For Each m In matches
        Debug.Print Trim(m.value) 'value will preserve any leading/trailing spaces
    Next

End Sub

注意到模式与劳雷尔的答案基本相同:

(\[[^\]]+\]|[^,]+)

如果您没有指定要匹配逗号，那么您就无法匹配它（无论是否存在） - 因此，上面的代码输出如下：

Available on 2 sides: Silkscreen
[full: color, covers entire face]
Pad Print: One color
[heat transfer, may bleed]

你可以轻松迭代 MatchCollection 来填充数组（如果需要的话）。

- Mathieu Guindon

---

你是指规范中写的“这些数据是供应商发送给我们的，我们无法控制他们输入什么或者如何输入”这句话吗？;-) - ThunderFrame

@ThunderFrame 你好坏啊 ;-) - Mathieu Guindon

我们应该全力推进一个解析项目。我会找出边缘情况，你来修复它们。;-p - ThunderFrame

@CaffeinatedCoder 我觉得在一个无关的评论串中“宣传”我的项目会让我感到不舒服，但是我的用户资料里有所有的链接（这是一个用于VBE的插件）。 - Mathieu Guindon

尽管如何，你完全有我的许可。毕竟你帮了我这么多，这是我能做的最小的回报。我一定会去看一看，因为我知道，对于你来说，这可能是对我极大的益处的东西。 - CaffeinatedMike

Answer 4

Function Splitter(s)
    Dim p As Long, b As Long, l As String
    Dim c As Long, s2 As String, arr, n

    If InStr(s, "[") = 0 Then
        arr = Split(s, ",")
    Else
        c = 0
        For p = 1 To Len(s)
            l = Mid(s, p, 1)
            If l = "," And c = 0 Then
                Mid(s, p, 1) = vbNull
            Else
                If l = "[" Then c = c + 1
                If l = "]" Then c = c - 1
            End If
        Next p
        arr = Split(s, vbNull)
    End If
    Splitter = arr
End Function

- Tim Williams

---

这个能不能通过在循环之前计算]的数量来改进呢？但即使如此，将其运行在可能每行有100多个字符的数千行数据上仍然会对资源造成负担，不是吗？ - CaffeinatedMike

如果您担心性能问题，那么测试非常简单。我在大约0.4秒的时间内运行了一个10k循环，对您的示例字符串进行了测试。 - Tim Williams