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?
The Microsoft Excel SPLIT function will split a string into substrings based on a delimiter. The result is returned as an array of substrings. The SPLIT function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a VBA function (VBA) in Excel.
To convert the split string into an array in VBA, we have a function called “SPLIT.” This VBA function. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.
The VBA Split Function is used is to split a string of text into an array. The text is split based on a given delimiter – e.g. a comma, space, colon etc. You can see that each item separated by the colon sign. We call the colon sign the delimiter.
Is this what you are trying to do? I have commented the code so you may not have a problem understanding it... And welcome to stackoverflow :)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With