如何在 vba 中的字符串数组中跳过拆分分隔字符串

Question

>我有一个VBA宏，我需要在其中获取splitStr(1( = str2，str3; splitStr(2( = str4;....

string1 = "str1,""str2,str3"",str4,str5,str6"
splitStr = Split(string1,",")
Debug.Print splitStr(1)
"str2    

谢谢

Answer 1

Split(expression [,delimiter] [,limit] [,compare] )函数是逐个字符搜索字符串字符，根据分隔符查找分隔符或子字符串。 您有以下几种选择：

1. 使用Split(string1,",")并在 splitStr 中获取生成的数组，如下所示：

   splitStr(0) = str1
   splitStr(1) = "str2
   splitStr(2) = str3"
   splitStr(3) = str4
   splitStr(4) = str5
   splitStr(5) = str6
   

然后使用以下代码以您想要的方式"修复"结果

splitStr(1) = Replace(splitStr(1), """", "") & Replace(splitStr(2), """", "")
'splitStr(1) now contains str2str3
'splitStr(2) still contains str3" 

Answer 2

拆分字符串，维护引号内的标记

尝试使用临时转换为 ►Byte类型的此方法，这样可以更快地循环字符串。

方法

基本上，引号内的所有逗号都会临时转换为另一个字符(例如分号(，这样您就可以像往常一样通过将最终分号替换回普通逗号来拆分。

请参阅以下代码示例中的进一步说明：

Sub testByte()
Const COMMA& = 44, QUOT& = 34, SEMI& = 59             ' constants for comma, quot.mark, semicolon
Dim b() As Byte, i As Long, bQU As Boolean
Dim string1$, sTemp$
' [0] string to be splitted
string1 = "str1,""str2,str3"",str4,str5,str6"
' [1] temporary conversion of commatas in quotations to another character using the BYTE type
b = string1                                           ' Assign string to bytes
For i = 0 To UBound(b) Step 2                         ' check each bytes representation of character
If b(i) = QUOT Then bQU = IIf(bQU, False, True)    ' check quotation starts
If bQU Then If b(i) = COMMA Then b(i) = SEMI       ' change commata in quotation to temporary semicolons
Next i
sTemp = b                                             ' convert bytes to normal string type
' [2] split as usual
Dim splitStr As Variant
splitStr = Split(sTemp, ",")
' [3] check all results
For i = 0 To UBound(splitStr)                          ' list all splitted items and ...
Debug.Print i, Replace(splitStr(i), ";", ",")      ' ... change semicolons back to normal commata
Next i
End Sub
'Results in immediate window
'---------------------------
'0            str1
'1            "str2,str3"
'2            str4
'3            str5
'4            str6

注意

当然，变量string1和sTemp中有一些有意的冗余，允许额外的测试：-(