Excel·VBA分列、字符串拆分

看到一篇博客《VBA，用VBA进行分列（拆分列）的2种方法》，使用VBA对字符串进行拆分

Excel分列功能

Sub 测试1()Range("a:a").TextToColumns other:=True, otherchar:="；"
End Sub

代码运行可以将左边拆分为右边，但分隔符只能有1个

将字符串拆分为二维数组，Split函数

分隔符只能有2个，且分隔符是有顺序的，具体看举例

Function str_split2d(ByVal source_str$, ByVal delimiter)'delimiter为分隔符数组，只能有2个元素；source_str按分隔符拆分为二维数组(数组从1开始计数)Dim s1$, s2$, srr, res, result, s, t, i&, j&, max_c&If UBound(delimiter) - LBound(delimiter) <> 1 Then Debug.Print "参数错误": Exit Functions1 = delimiter(LBound(delimiter)): s2 = delimiter(UBound(delimiter))srr = Split(source_str, s1): ReDim res(1 To UBound(srr) - LBound(srr) + 1, 1 To 10 ^ 3)For Each s In srrtemp = Split(s, s2): i = i + 1: j = 0For Each t In tempj = j + 1: res(i, j) = tNextIf j > max_c Then max_c = jNextReDim result(1 To i, 1 To max_c)  '结果数组For i = 1 To UBound(res)For j = 1 To max_cresult(i, j) = res(i, j)NextNextstr_split2d = result
End Function

举例

Sub 测试2()Dim resres = str_split2d([a1], Array(";", ","))[d1].Resize(UBound(res), UBound(res, 2)) = res
End Sub

将字符串拆分为一维数组，正则表达式

Split函数虽然能够拆分字符串，但是如果分隔符数量较多，那么就需要不断循环遍历分隔符，对拆分后的字符串继续进行拆分，这显然是比较麻烦的
而正则表达式是处理字符串的强大工具，对于处理多个分隔符，代码较为简单
更多关于正则表达式内容详见，《Excel·VBA自定义正则表达式函数、使用》

Function 正则拆分字符串(ByVal source_str$, ByVal delimiter$)'delimiter为分隔符，source_str按分隔符拆分为一维数组(数组从1开始计数)Dim pat$, result, i&, num&pat = "[^" & delimiter & "]"  '正则匹配模式，^非With CreateObject("vbscript.regexp")  '正则表达式.Global = True.Pattern = patSet mhs = .Execute(source_str): num = mhs.CountIf num = 0 Then 正则拆分字符串 = WorksheetFunction.Transpose(WorksheetFunction.Transpose(Array(source_str))): Exit FunctionReDim result(1 To num)For i = 0 To num - 1result(i + 1) = mhs(i).ValueNext正则拆分字符串 = resultEnd With
End Function

举例

Sub 测试3()Dim resres = 正则拆分字符串([a1], ",;")[a3].Resize(UBound(res), 1) = WorksheetFunction.Transpose(res)res = 正则拆分字符串([d1], ",;|")[d3].Resize(UBound(res), 1) = WorksheetFunction.Transpose(res)
End Sub