How to split string between commas and store each in an array variable using vba

Question

I have a question: How can I split the string between commas which are inside open and closed parentheses and store each in an array variable?

Example:

strinput = "( u1 u1t_a, u2 u2t_b, s2 s2t_c, s4 s4t_d, ...n )"

Having an input string above, I want to store in an array variable the three and so on substring between commas which are inside open and closed parentheses :

substr(0) = "u1 u1t_a"
substr(1) = "u2 u2t_b"
substr(2) = "s2 s2t_c"
substr(n) = "...n"

As of now, I am having difficulty of using loop together with array in VBA so my code is like a brute force which can only process a maximum of 3 text since the code will became long so I made a limit.

See my code here:

strinput = "( u1 u1t_a, u2 u2t_b, s2 s2t_c )"

substr1 = Right(strinput, Len(strinput) - Find("(", strinput))
    'Output: u1 u1t_a, u2 u2t_b, s2 s2t_c )
substr1f = Left(substr1, Find(",", substr1) - 1)
    'Output: u1 u1t_a

substr2 = Right(substr1, Len(substr1) - Find("(", substr1))
    'Output: u2 u2t_b, s2 s2t_c )
substr2f = Left(substr2, Find(",", substr2) - 1)
    'Output: u2 u2t_b

substr3 = Right(substr2, Len(substr2) - Find("(", substr2))
    'Output: s2 s2t_c )
substr3f = Left(substr3, Find(")", substr3) - 1)
    'Output: s2 s2t_c

How can I make this loop?

Answer 1

Option Explicit

Sub Sample()
    Dim Ar As Variant
    Dim strinput  As String, s As String
    Dim i As Long
    
    strinput = "( u1 u1t_a, u2 u2t_b, s2 s2t_c, s4 s4t_d, ...n )"
    
    '~~> Replace ( and ) with ""
    s = Replace(Replace(strinput, ")", ""), "(", "")
    
    '~~> Split and store in an arry based on ","
    Ar = Split(s, ",")
    
    '~~> See what is there in the array
    For i = LBound(Ar) To UBound(Ar)
        Debug.Print Ar(i)
    Next i
End Sub

If you want to combine the Replace and Split then you can use this as well

Option Explicit

Sub Sample()
    Dim Ar As Variant
    Dim strinput  As String
    Dim i As Long
    
    strinput = "( u1 u1t_a, u2 u2t_b, s2 s2t_c, s4 s4t_d, ...n )"
            
    Ar = Split(Split(Split(strinput, "(")(1), ")")(0), ",")
    
    '~~> See what is there in the array
    For i = LBound(Ar) To UBound(Ar)
        Debug.Print Ar(i)
    Next i
End Sub