regex - 替换字符串中的所有字符，除非它们位于双引号内

Question

遗憾的是，我不熟悉正则表达式，因为我不是程序员，但我猜想使用正则表达式可以轻松解决这个问题(不过，我绝对愿意接受其他建议)

我想使用 split 函数来拆分单元格的值并将其分散到多个单元格中。分隔符是逗号。但问题是，例如，某些用户在注释中使用逗号，Split 函数使用逗号来拆分注释中的字符串。

例如包含值的单元格:

0001,"name","address","likes apples, oranges and plums"

需要分成多个单元格，分别表示 0001“姓名”“地址”和“喜欢苹果、橙子和李子”。

我的代码也分割了注释，我希望它忽略注释或带双引号的其他所有内容。这是一个示例:

Sub SplittingStrings()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strInput As String
    Dim counter As Integer
    Dim cell As Variant
    Dim splitCount As Integer
    Dim splitString() As String
    Dim category As Variant

    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet

    counter = 1

    For Each cell In Range("A1", "A2000")

        If cell.Value <> "" Then
            strInput = cell.Value
            splitCount = 2
            splitString = Split(strInput, ",")

            For Each category In splitString
                Cells(counter, splitCount).Value = category
                splitCount = splitCount + 1
            Next category
        End If

        counter = counter + 1

    Next cell


End Sub

如何排除 split 函数考虑的带双引号的内容？

Answer 1

请尝试一下，看看是否能获得所需的输出。

根据需要调整变量。

Sub SplittingStringsUsingRegEx()
Dim lr As Long, c As Long
Dim Rng As Range, cell As Range
Dim RE, Match, Matches

Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range("A1:A" & lr)

Set RE = CreateObject("VBScript.RegExp")
With RE
    .Global = True
    .Pattern = "\d+|"".+?"""
End With

c = 2

For Each cell In Rng
    If RE.test(cell.Value) Then
        Set Matches = RE.Execute(cell.Value)
        For Each Match In Matches
            Cells(cell.Row, c) = Replace(Match, """", "")
            c = c + 1
        Next Match
    End If
    c = 2
Next cell

Application.ScreenUpdating = True

End Sub