***使用变体数组而不是单元格循环 - 一旦数据集有意义，它们在代码方面会更快.即使代码更长:)

You are better off using variant arrays rather than cell loops - they are much quicker code wise once the data sets are meaningful. Even thoug the code is longer :)

下面的这个示例转储到 C 和 D 列，以便您可以看到原始数据.将 [c1].Resize(lngCnt, 2).Value2 = Application.Transpose(Y) 改为 [a1].Resize(lngCnt, 2).Value2 = Application.Transpose(Y) 转储原始数据

This sample below dumps to column C and D so that you can see the orginal data. Change [c1].Resize(lngCnt, 2).Value2 = Application.Transpose(Y) to [a1].Resize(lngCnt, 2).Value2 = Application.Transpose(Y) to dump over your original data

[用正则表达式更新以删除,之后的任何空格，即,band"变成band"]

[Updated with regexp to remove any blanks after , ie ", band" becomes "band"]

Sub SliceNDice() 
Dim objRegex As Object 
Dim X 
Dim Y 
Dim lngRow As Long 
Dim lngCnt As Long 
Dim tempArr() As String 
Dim strArr 
Set objRegex = CreateObject("vbscript.regexp") 
objRegex.Pattern = "^s+(.+?)$" 
 'Define the range to be analysed
X = Range([a1], Cells(Rows.Count, "b").End(xlUp)).Value2 
Redim Y(1 To 2, 1 To 1000) 
For lngRow = 1 To UBound(X, 1) 
     'Split each string by ","
    tempArr = Split(X(lngRow, 2), ",") 
    For Each strArr In tempArr 
        lngCnt = lngCnt + 1 
         'Add another 1000 records to resorted array every 1000 records
        If lngCnt Mod 1000 = 0 Then Redim Preserve Y(1 To 2, 1 To lngCnt + 1000) 
        Y(1, lngCnt) = X(lngRow, 1) 
        Y(2, lngCnt) = objRegex.Replace(strArr, "$1") 
    Next 
Next lngRow 
 'Dump the re-ordered range to columns C:D
[c1].Resize(lngCnt, 2).Value2 = Application.Transpose(Y) 
End Sub