none
How can we use SPLIT to split all the elements of a 1-dimensional variant array? RRS feed

  • Question

  • Hello,

    I use the SPLIT function to split a single string into an array:

    text = "A|B|C|D"

    array1() = SPLIT (text, "|")

    ------------------

    Suppose now we have a 1-dimensional variant array which contains many rows like:

    A|B|C|D

    E|F|G|H

    I|J|K|L

    etc.

    How can we use SPLIT to split all the elements in the 1-d variant array and put the results in a 2-d variant array?

    The 2-d array will have 4 columns, like this:

    A   B   C   D

    E   F   G   H

    I   J   K   L

    etc.

    Please help with some sample code.

    Thanks

    Leon

    Thursday, December 12, 2019 11:00 AM

Answers

  • For example:

    Sub test()
        Dim a
        Dim i As Long
        Dim j As Long
        Dim p
        a = Array("A|B|C|D", "E|F|G|H", "I|J|K|L")
        ReDim b(LBound(a) To UBound(a), 0 To 3) As String
        For i = LBound(a) To UBound(a)
            p = Split(a(i), "|")
            For j = 0 To 3
                b(i, j) = p(j)
            Next j
        Next i
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Leon Lai Friday, December 13, 2019 7:28 AM
    Thursday, December 12, 2019 1:15 PM

All replies

  • For example:

    Sub test()
        Dim a
        Dim i As Long
        Dim j As Long
        Dim p
        a = Array("A|B|C|D", "E|F|G|H", "I|J|K|L")
        ReDim b(LBound(a) To UBound(a), 0 To 3) As String
        For i = LBound(a) To UBound(a)
            p = Split(a(i), "|")
            For j = 0 To 3
                b(i, j) = p(j)
            Next j
        Next i
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Leon Lai Friday, December 13, 2019 7:28 AM
    Thursday, December 12, 2019 1:15 PM
  • Hello Hans Vogelaar

    Thanks a lot for your code.

    It works great for my project: to search as you type through 1 million rows.

    As I cannot FILTER a 2-d array (but only a 1-d array), I have concatenated all fields in my Excel Range (1 million rows) so as to obtain a single column.

    Then I convert this Excel Range to a 1-d Array. I can apply FILTER to this 1-d array based on the string I typed in the combobox.

    Then I apply your code to the filtered array to produce a 2-d array. I copy this filtered array to the combobox, and Lo! its dropdown shows 4 perfectly aligned columns (I could not do this with 1-d array!)

    Thanks to using CONCATENATION, FILTER and SPLIT, I am happy I could develop a very rapid search as you type add-in. With 1 million rows, it's a bit slow, but with 200,000 (more than I need), it's very fast.

    Thanks again for your precious help.

    Regards,

    Leon


    • Edited by Leon Lai Friday, December 13, 2019 9:06 AM
    Friday, December 13, 2019 7:28 AM