Alex
Alex

Reputation: 73

How to split a string into a 2-dimensional array in vba?

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

Answers (4)

Meer Molix
Meer Molix

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:

  1. Watch out the data type of those variables and arrays;
  2. Watch out the subscript of those arrays;
  3. The formula reference style must be xlA1,you can make this mandatory setting by VBA code or set it in the option menu by unchecking R1C1 reference style, otherwise Index function will return "Error 2015"

And thank you! @JvdV

Upvotes: 0

Slai
Slai

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

JvdV
JvdV

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

Teamothy
Teamothy

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:

Watch of 2d array

etc.

Upvotes: 3

Related Questions