vba 如何在vba excel中用多个分隔符分割字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7680490/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to split a string with multiple delimiters in vba excel?
提问by infinitloop
I want to split a string with multiple delimiters using Excel VBA. One of the strings is:
我想使用 Excel VBA 拆分具有多个分隔符的字符串。其中一个字符串是：
d1-d2 d3 d4
We have a dash and a space as two delimiters. I tried the splitfunction but it only does it with one delimiter.
我们有一个破折号和一个空格作为两个分隔符。我尝试了这个split函数，但它只用一个分隔符来完成。
回答by PaulStock
You could first do a Replaceon the string first and then do the split:
你可以先Replace在字符串上做一个，然后再做拆分：
newString = Replace(origString, "-", " ")
newArray = Split(newString, " ")
回答by Michael Merchant
The previous answer is good, but will cause you to have trouble if there are back to back characters to be split on that are in the String, such as splitting "Hello, Sir! How are you doing, today?" on all punctuation and spaces. In this case, you would get a blank string between Hello and Sir.
前面的答案很好，但是如果字符串中有要拆分的背靠背字符，则会给您带来麻烦，例如拆分“您好，先生！今天好吗？” 所有标点符号和空格。在这种情况下，您将在 Hello 和 Sir 之间得到一个空白字符串。
To handle this scenario Chip Pearson provides a great VBA function for use: http://www.cpearson.com/excel/splitondelimiters.aspx
为了处理这种情况，Chip Pearson 提供了一个很棒的 VBA 函数供使用：http: //www.cpearson.com/excel/splitondelimiters.aspx
回答by rejdrouin
To split with several different delimiters ; list the delimiters in an array, replace them with a for loop, then split :
用几个不同的分隔符分割；列出数组中的分隔符，用 for 循环替换它们，然后拆分：
For Each tSep In Array(";", " ", ".", "<==", ":", vbCr)
val1 = Replace(val1, tSép, "°")
Next tSep
tab1 = Split(val1, "°")
回答by Reged
Not allowed to comment (yet) but suggestion of using TRIM to eliminate a double space is not fully clear. The TRIM function in VBA only deletes leading and trailing spaces. It does not touch double spaces inside a string. You would have to use the worksheet function for that.
不允许评论（还），但使用 TRIM 消除双空格的建议尚不完全清楚。VBA 中的 TRIM 函数只删除前导和尾随空格。它不会触及字符串内的双空格。为此，您必须使用工作表功能。
回答by Greedo
I'll add that I had a quick look at Chip Pearson's answer, and thought it could be improved a little in terms of performance, so I wrote my own which appears to be about 40% faster (feel free to test yourself). It's faster (1.0E-5vs 1.7E-5seconds per cycle) because it uses byte arrays rather than actual characters to compare values. Here's the function which returns a string array like Chip Pearson's:
我要补充一点，我快速浏览了 Chip Pearson 的答案，并认为它可以在性能方面有所改进，所以我写了自己的答案，它似乎快了 40%（随意测试自己）。它更快（1.0E-5vs1.7E-5每个周期的秒数），因为它使用字节数组而不是实际字符来比较值。这是返回类似于 Chip Pearson 的字符串数组的函数：
Function SplitMultiDelims2(Text As String, DelimChars As String) As String()
'''
'Function to split a string at multiple charachters
'Use like SplitMultiDelims2("This:is-a,test string", ":-,")
'Returns an array, in that example SplitMultiDelims2("This:is-a,test string", ":-,")(4) would be "test string"
'''
Dim bytes() As Byte
Dim delims() As Byte
Dim i As Long, aub As Long, ub As Long
Dim stack As String
Dim t() As String
Dim tLen As Long
tLen = Len(Text)
If tLen = 0 Then
Exit Function
End If
ReDim t(1 To tLen) 'oversize array to avoid Redim Preserve too often
bytes = StrConv(Text, vbFromUnicode)
delims = StrConv(DelimChars, vbFromUnicode)
ub = UBound(bytes)
For i = 0 To ub
If Contains(delims, bytes(i)) Then
aub = aub + 1
t(aub) = stack
stack = ""
Else
stack = stack & Chr(bytes(i))
End If
Next i
t(aub + 1) = stack
ReDim Preserve t(1 To aub + 1) 'Works marginally faster if you delete this line,
'however it returns an oversized array (which is a problem if you use UBOUND of the result,
'but fine if you are just looking up an indexed value like the 5th string)
SplitMultiDelims2 = t
End Function
'and a 2nd function called by the first one
Function Contains(arr, v As Byte) As Boolean 'checks if Byte v is contained in Byte array arr
Dim rv As Boolean, lb As Long, ub As Long, i As Long
lb = LBound(arr)
ub = UBound(arr)
For i = lb To ub
If arr(i) = v Then
rv = True
Exit For
End If
Next i
Contains = rv
End Function
Here's the test log (his is SplitMultiDelims, mine is SplitMultiDelims2)
这是测试日志（他是 SplitMultiDelims，我的是 SplitMultiDelims2）
> SplitMultiDelims: 1.76105267188204E-05s per cycle 'this is the important figure
> i = 568064 iterations in 10.00390625 seconds
>Test completed: 08/06/2017 10:23:22
> SplitMultiDelims2: 1.05756701906142E-05s per cycle
>i = 947044 iterations in 10.015625 seconds
>Test completed: 08/06/2017 10:23:32
> SplitMultiDelims2: 1.04176859354441E-05s per cycle
>i = 960656 iterations in 10.0078125 seconds
>Test completed: 08/06/2017 10:23:54
> SplitMultiDelims: 1.76228941673255E-05s per cycle
>i = 567887 iterations in 10.0078125 seconds
>Test completed: 08/06/2017 10:24:04
Run in both directions to avoid memory writing handicaps
双向运行以避免内存写入障碍
Test code below uses Timerso not overly precise, but good enough to demonstrate the difference
下面的测试代码使用Timer不太精确，但足以证明差异
Sub testSplit()
Dim t As Double, dt As Double
Dim s As String
Dim i As Long
t = Timer: i = 0: dt = 0: s = ""
Do Until dt > 10 'loop for 10 seconds
s = SplitMultiDelims("This:is-a,test string", ":-,")(1)
dt = Timer - t
i = i + 1
Loop
Debug.Print "SplitMultiDelims: " & dt / i & "s per cycle" & vbCrLf & "i = " & i; " iterations in " & dt; " seconds" & vbCrLf & "Test completed: " & Now
t = Timer: i = 0: dt = 0: s = ""
Do Until dt > 10 'loop for 10 seconds
s = SplitMultiDelims2("This:is-a,test string", ":-,")(1)
dt = Timer - t
i = i + 1
Loop
Debug.Print "SplitMultiDelims2: " & dt / i & "s per cycle" & vbCrLf & "i = " & i; " iterations in " & dt; " seconds" & vbCrLf & "Test completed: " & Now
End Sub
回答by Dave Odle
in which case you could do
在这种情况下你可以做
newString = Replace(origString, "-", " ")
newString2 = replace(newstring, " " , " ")
newArray = SPlit(newString, " ")
