Excel VBA formula for split rows and columns doesn't work

[haas67]

Hi All,

I've made a function like textsplit in VBA. I want to split a delimited string in rows and columns.

The function is:
Function Splitter3(data As String, col_delim As String, Optional row_delim As String) As Variant()
Dim arr1() As String
Dim arr2() As String
Dim result() As Variant

If row_delim <> "" Then
arr1 = Split(data, row_delim)
ReDim result(UBound(arr1))

For i = 0 To UBound(arr1)
arr2 = Split(arr1(i), col_delim)
result(i) = arr2
Next
Else
ReDim result(0)

arr2 = Split(data, col_delim)
result(0) = arr2
End If

Splitter3 = result
End Function

It works fine if the data string is made up of equal columns, but not if one column is missing from a row.

Formula: =Splitter3(A1, ",", ";")
Not working: "Apple,Orange,Peach;Strawberry,Mango,Grape;Raspberry,Kiwi"
Working: "Apple,Orange,Peach;Strawberry,Mango,Grape;Raspberry,Kiwi,x"

Do you see my mistake?

Regards,
Haas67

 

[Alex Blakenburg]

I only have 365, does your splitter function and unique function work in 2016 ?
On the face of it I would have thought they needed dynamic array / spill functionality to work.

 

[haas67]

I only have 365, does your splitter function and unique function work in 2016 ?
On the face of it I would have thought they needed dynamic array / spill functionality to work.

On 365, they automatically spill.
On 2016, you select the cells, enter the formula in the active cell, and press ctrl-shift-enter.

 

[Alex Blakenburg]

Had not thought to do that with a UDF. Good to know. Thanks for sharing.