# How can we use SPLIT to split all the elements of a 1-dimensional variant array? • ### 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.

Thanks

Leon

Thursday, December 12, 2019 11:00 AM

• 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 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 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 Friday, December 13, 2019 9:06 AM
Friday, December 13, 2019 7:28 AM