regex - 连接字符串并删除多余的逗号

Question

我正在尝试连接多个字符串并用逗号分隔它们，然后删除多余的前导逗号和尾随逗号。

例如，输入 TEST("", "b", "c", "", "")，我想得到b、c

但是，我的正则表达式 ,$| ,+|^, 并没有真正考虑重复的逗号:

Function TEST(a, b, c, d, e)
    res = a & ", " & b & ", " & c & ", " & d & ", " & e

    Debug.Print (res)
    Dim regex As Object, str, result As String
    Set regex = CreateObject("VBScript.RegExp")

    With regex
      .Pattern = ",$| ,+|^,"
    End With

    Dim ReplacePattern As String
    ReplacePattern = ""

    res = regex.Replace(res, ReplacePattern)

    TEST = res
End Function

我该怎么做？

Answer 1

最优雅的是@ScottCraner 的建议 TEXTJOIN (如果他希望将其作为自己的内容发布，将删除这部分答案)

Private Function nonEmptyFields(ParamArray strings() As Variant) As String
    nonEmptyFields = WorksheetFunction.TextJoin(",", True, Array(strings))
End Function

Note: This will only work for Office 365+, but you can always create your own version of TEXTJOIN

<小时/>

另一种选择是循环遍历字符串的 ParamArray 并将它们添加在一起，具体取决于它们的内容(无论它们是填充还是空)

Private Function nonEmptyFields(ParamArray strings() As Variant) As String

    Dim result As String
    Dim i As Byte

    For i = LBound(strings) To UBound(strings)
        If Len(strings(i)) <> 0 Then
            If result = vbNullString Then
                result = strings(i)
            Else
                result = result & "," & strings(i)
            End If
        End If
    Next i

    nonEmptyFields = result

End Function

通过设置两者都会产生期望的结果

    Debug.Print nonEmptyFields(a, b, c, d, e, f) ' "", "b", "c", "", "", ""