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
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