Concatenate strings and remove excess comma

Question

I'm trying to concatenate multiple strings and separate them by comma, and then subsequently to remove excess, leading and trailing commata.

For example, with an input of TEST("", "b", "c", "", ""), I would like to get b, c

However, my regex ,$| ,+|^, does not really take repeated commas into account:

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

How can I do this?

If not, here is some code that mimics it: https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell — Scott Craner, Apr 08 '19 at 15:36
Yes, I see. Just figuring out how to put my function arguments into an array, `res = TEXTJOIN(",", True, {a,b,c,d,e})` does not work... — user1981275, Apr 08 '19 at 15:51
Just a quick note, in your example, it's supposed to say "I would like to get `b`, `c`", right? — Samuel Hulla, Apr 08 '19 at 15:53
One more thing I'm slightly confused about. Is the argument in your `TEST` function supposed to be a `ParamArray` of arguments or one large `String` ? — Samuel Hulla, Apr 08 '19 at 16:07
Perhaps try `TEXTJOIN(",", True, array("a","b","c","d","e"))`. — SJR, Apr 08 '19 at 16:10

Samuel Hulla · Accepted Answer · 2019-04-08T16:51:11.370

Most elegant is @ScottCraner's suggestion of TEXTJOIN (will remove this part of answer, if he wishes to post this as his own)

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

Another option would be to loop over the ParamArray of strings and add them together, depending on their content (whether they are populated or empty)

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

Both would yield desired result with set up of

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

Too lazy today to answer. I like this, simple and straight forward. — Scott Craner, Apr 08 '19 at 17:09

score 1 · Answer 2 · answered Apr 08 '19 at 16:20

1

My ugly solution maintaining the same parameters:

Function TEST(a, b, c, d, e)

    If a <> "" Then res = a

    If b <> "" Then
        If res <> "" Then
            res = res & ", " & b
        Else
            res = b
        End If
    End If

    If c <> "" Then
        If res <> "" Then
            res = res & ", " & c
        Else
            res = c
        End If
    End If

    If d <> "" Then
        If res <> "" Then
            res = res & ", " & d
        Else
            res = d
        End If
    End If

    If e <> "" Then
        If res <> "" Then
            res = res & ", " & e
        Else
            res = e
        End If
    End If

    TEST = res

End Function

answered Apr 08 '19 at 16:20

dwirony

5,487
3
21
43

I feel like it would be a better idea to do this with dynamic amount of arguments (either the MS way of many `Optional` arguments or a `ParamArray`) – Samuel Hulla Apr 08 '19 at 16:35
Definitely - if I could loop through the parameters that'd be great, I'm just keeping it the way they had it setup initially. – dwirony Apr 08 '19 at 16:37
Technically you can, if you were to name the arguments in form of an ordered list (eg. `Optional ByVal string1 As String, Optional ByVal string2 As String, ...` then you can just loop over the variable names with a `for` loop and `IsMissing()`. But yeah, probably more hassle than it's worth, especially if all that can be solved with one simple `ParamArray` – Samuel Hulla Apr 08 '19 at 16:46

Concatenate strings and remove excess comma

2 Answers2