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?

Start with counting the number of commas in your string. Then make a loop. — Luuklag
– Luuklag, Commented Sep 30, 2018 at 16:28

Dharman · Accepted Answer · 2024-02-02 16:39:44Z

10

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

edited Feb 2, 2024 at 16:39

Dharman♦

33.9k27 gold badges104 silver badges157 bronze badges

answered Sep 30, 2018 at 16:30

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

marubindesu Over a year ago

Thank you very much, Mr. Rout for your response, I tried the code and it works just like what I've wanted. Also because of your answer, I just realize the idea of having to replace first the open and closed parentheses, then focus on string between commas, that's an awesome technique and problem solving I just learned. For now, I will learn the functions of LBound and UBoundin the code. Also, thank you for providing comments so that I can understand your code, I really appreciate it! Having my first question, with this welcoming answer really makes me feel welcomed. Thank you very much!

Gary's Student · Accepted Answer · 2018-09-30 16:30:00Z

4

How about:

Sub qwerty()

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

    For Each a In arr
        MsgBox a
    Next a
End Sub

answered Sep 30, 2018 at 16:30

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

2 Comments

marubindesu Over a year ago

Thank you so much for your response, I tried this and it works. But I have additional question, I don't understand the variable a, how does it able to output the correct string? Like how does variable a works together with variable arr in loop? Thank you very much!

Gary's Student Over a year ago

@VinIsLearning the variable a represents an individual item in the array arr()

DisplayName · Accepted Answer · 2018-09-30 22:05:08Z

0

Another flavour:

Sub MySub()
    Dim strinput As String, a As Variant
    strinput = "( u1 u1t_a, u2 u2t_b, s2 s2t_c, s4 s4t_d, ...n )"
    For Each a In Split(Mid$(strinput, 3, Len(strinput) - 4), ", ") ‘ first extract a substring out of your string leaving off first two and last two characters, then Split resulting string using commas as delimiter
        MsgBox a
    Next
End Sub

You can see here for Mid$() function

answered Sep 30, 2018 at 22:05

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Comments

Dy.Lee · Accepted Answer · 2018-10-01 02:53:12Z

0

You can use the trim function to remove blank spaces.

Sub test()
    Dim substr As Variant
    Dim strinput As String
    Dim i As Long

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

    strinput = Replace(strinput, "(", "")
    strinput = Replace(strinput, ")", "")

    substr = Split(strinput, ",")

    For i = LBound(substr) To UBound(substr)
        substr(i) = Trim(substr(i))
        Debug.Print substr(i)
    Next i
End Sub

answered Oct 1, 2018 at 2:53

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related