Reputation: 73
I want to be able to split a formatted string (such as the one following) into an array (which I will then use later on).
Example string: (x, f), (5, 6), (6, 1), (7, 8), (8, 5), (9, 5), (10, 5), (11, 3), (12, 4), (13, 1), (14, 6), (15, 2), (16, 10)
Each set of brackets would be one dimension, and the numbers within would constitute another. For example strArray (4, 2) would result in bracket 4, number 2 -> which is 8 in this instance.
I've thought about brute forcing it by using multiple SPLIT functions, and I've looked into Regular Expressions to do it. I'm confident in making a 1-dimensional array, but struggling with a 2-dimensional array.
I am using Microsoft Excel 2016, and am an intermediate programmer in VBA.
This is one approach I've tried to process each bracket, but it doesn't result in a 2-dimensional array:
Sub EvaluateString(txtString as String)
txtArray = Split(txtSTring, "),(")
If IsEmpty(txtArray) Then L=0 Else L=UBound(txtArray)
'remove leading "(" and last ")"
txtArray(0) = Replace(txtArray(0), "(", "")
txtArray(L) = Replace(txtArray(L), ")", "")
For i = 0 to L
'Do something
next i
End Sub
Upvotes: 4
Views: 1639
Reputation: 1
This is a supplement to JvdV's answer:
I must admit it's impressive and enlightening. I guess I'm not the only one who always seeking for a general method to convert a string into 2-Dimensional array without looping and spliting substring populated by row delimiter. I've known that Application.Transpose will convert 1-D array which contains 1-D subarray as element into 2-D array, but I never realized that it can be combined with Index function in such an elegant way!
Now I extend this method to populate any customized size 2-D array in my project, only if I know the column number of that array, take an example:
Assume that I get a Recordset from ADO object by SQL query and retrieve data by .GetString Method (I can't use GetRows method becuz of Null value in the recordset), then divided the string into a N*4 size 2-dimensional array:
Dim adoCnnctn As New ADODB.Connection
Dim strRcrdSt As String
Dim arrRcrdSt As Variant
Dim arrFld1 As Variant, arrFld2 As Variant, arrFld3 As Variant, arrFld4 As Variant
strRcrdSt = adoCnnctn.Execute(strQry).GetString(adClipString, , ",", ",", "N/A")
arrRcrdSt = Split(Left(strRcrdSt, Len(strRcrdSt) - 1), ",")
With Application
.ReferenceStyle = xlA1
arrFld1 = Evaluate("=TRANSPOSE((ROW(1:" & (UBound(arrRcrdSt) + 1) / 4 & ")*4)-3)")
arrFld2 = Evaluate("=TRANSPOSE((ROW(1:" & (UBound(arrRcrdSt) + 1) / 4 & ")*4)-2)")
arrFld3 = Evaluate("=TRANSPOSE((ROW(1:" & (UBound(arrRcrdSt) + 1) / 4 & ")*4)-1)")
arrFld4 = Evaluate("=TRANSPOSE((ROW(1:" & (UBound(arrRcrdSt) + 1) / 4 & ")*4)-0)")
arrRcrdSt = .Transpose(Array( _
.Index(arrRcrdSt, 1, arrFld1), _
.Index(arrRcrdSt, 1, arrFld2), _
.Index(arrRcrdSt, 1, arrFld3), _
.Index(arrRcrdSt, 1, arrFld4)))
End With
Key points you must pay attetion to:
And thank you! @JvdV
Upvotes: 0
Reputation: 22876
Excel 2D array format is {"x", "f"; "5", "6"; "6", "1"}
, so few replaces should be enough :
Sub EvaluateString(txtString as String)
txtString = Replace(Replace(txtString, " ", ""), "),(", """;""")
txtString = Replace(Replace(Replace(txtString, "(", "{"""), ")", """}"), ",", """,""")
txtArray = Evaluate(txtString) ' txtArray is now 2D array Variant(1 to x, 1 to 2)
End Sub
Note that Application.Evaluate
method is limited to 255 characters.
Upvotes: 2
Reputation: 75870
Interesting question. I would suggest making use of a couple of Application
methods to "slice" two 1D-arrays from your original string, and then "join" them into a single array through Transpose
.
Sub Test()
'Prepare your string
Dim str As String: str = "(x, f), (5, 6), (6, 1), (7, 8), (8, 5), (9, 5), (10, 5), (11, 3), (12, 4), (13, 1), (14, 6), (15, 2), (16, 10)"
str = Replace(Replace(Replace(str, " ", ""), "(", ""), ")", "")
'Prepare your array
Dim arr As Variant: arr = Split(str, ",")
Dim lb As Long: lb = UBound(arr) + 1
'Prepare two "slicer" 1D-arrays
Dim slc1 As Variant: slc1 = Evaluate("TRANSPOSE((ROW(1:" & (lb / 2) & ")*2)-1)")
Dim slc2 As Variant: slc2 = Evaluate("TRANSPOSE(ROW(1:" & (lb / 2) & ")*2)")
'Slice your array and transpose it into a a single array to be used
With Application
arr = .Transpose(Array(.Index(arr, 1, slc1), .Index(arr, 1, slc2)))
End With
Debug.Print arr(4, 2)
End Sub
Upvotes: 5
Reputation: 2016
If I understand You correctly, try this:
Sub test2()
Dim txtstring As String
txtstring = "(x, f),(5, 6),(6, 1),(7, 8),(8, 5),(9, 5),(10, 5),(11, 3),(12, 4),(13, 1),(14, 6),(15, 2),(16, 10)"
Call EvaluateString(txtstring)
End Sub
Sub EvaluateString(txtstring As String)
Dim txtArray2d As Variant
txtArray = Split(txtstring, "),(")
If IsEmpty(txtArray) Then L = 0 Else L = UBound(txtArray)
'remove leading "(" and last ")"
txtArray(0) = Replace(txtArray(0), "(", "")
txtArray(L) = Replace(txtArray(L), ")", "")
ReDim txtArray2d(0 To L, 1 To 2)
For i = 0 To L
txtArray2d(i, 1) = Split(txtArray(i), ",")(0)
txtArray2d(i, 2) = Trim(Split(txtArray(i), ",")(1))
Next i
End Sub
and taking your string variant as example, 2d array values look like this:
etc.
Upvotes: 3