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

 

[nemmi69]

what should the result look like?

 

[haas67]

In the case when it is working, the reponse is a spilling range.
But in the case it is not working it is returning a #VALUE! error.

 

[igold]

It works fine for me. I used a 1 element string up to a 12 element string.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Apple,Orange,Peach,Strawberry,Mango,Grape,Raspberry,Kiwi,s,d,f,g
2	Apple,Orange,Peach,Strawberry
3	Apple
4	Apple,Orange,Peach,Strawberry,Mango,Grape,Raspberry,Kiwi,x
5
6										Apple	Orange	Peach	Strawberry	Mango	Grape	Raspberry	Kiwi	s	d	f	g
7										Apple	Orange	Peach	Strawberry
8										Apple
9										Apple	Orange	Peach	Strawberry	Mango	Grape	Raspberry	Kiwi	x
10
11
Sheet1

Cell Formulas
Range	Formula
J6:U6,J9:R9,J8,J7:M7	J6	=Splitter3(A1,",")
Dynamic array formulas.

 

[haas67]

Did you actually try my mentioned failing string? The one in cell A1 from the picture

 

[igold]

Yes.
I had thought the semicolons were just typos, but here you go...

Edit: I see now that you want to split out the semicolon as well. Let me check this out.

Splitter Function.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Apple,Orange,Peach;Strawberry,Mango,Grape;Raspberry,Kiwi
2	Apple,Orange,Peach,Strawberry
3	Apple
4	Apple,Orange,Peach,Strawberry,Mango,Grape,Raspberry,Kiwi,x
5
6										Apple	Orange	Peach;Strawberry	Mango	Grape;Raspberry	Kiwi
7										Apple	Orange	Peach	Strawberry
8										Apple
9										Apple	Orange	Peach	Strawberry	Mango	Grape	Raspberry	Kiwi	x
10
Sheet1

Cell Formulas
Range	Formula
J6:O6,J9:R9,J8,J7:M7	J6	=Splitter3(A1,",")
Dynamic array formulas.

 

[haas67]

Yes, the semicolon is used to split in rows, the comma to split in columns.
And if the columns are not even size, the #Value! error occurs.

Just like the new TEXTSPLIT function in Excel 365.
But since I don't have that (yet), and i also use 2016 sometimes, I thought i'd make a macro for this.

 

[igold]

Unfortunately I must be missing something. I am getting an error with the string that you say is working correctly (Cell A2).

It may be helpful if you would use XL2BB to post your data.

 

[haas67]

Here is the mini-sheet.

Textjoin2016.xlsm
	A	B	C	D	E	F	G	H
1	Apple,Orange,Peach;Strawberry,Mango,Grape;Raspberry,Kiwi
2	Apple,Orange,Peach;Strawberry,Mango,Grape;Raspberry,Kiwi,x
3
4	Apple	Orange	Peach
5	Strawberry	Mango	Grape
6	Raspberry	Kiwi
7
8
9
10	#VALUE!				{=Splitter3(A1,",",";")}
11
12
13
14	Apple	Orange	Peach		{=Splitter3(A2,",",";")}
15	Strawberry	Mango	Grape
16	Raspberry	Kiwi	x
17
Split

Cell Formulas
Range	Formula
A10	A10	=Splitter3(A1,",",";")
E10,E14	E10	=FORMULATEXT(A10)
A14:C16	A14	=Splitter3(A2,",",";")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[igold]

This is what my sheet looks like with your mini sheet copied over. I am using your code. I had to remove "Option Explicit" from my module as you have not declared "i" as long.

Splitter Function.xlsm
	A	B	C	D	E	F
1	Apple,Orange,Peach;Strawberry,Mango,Grape;Raspberry,Kiwi
2	Apple,Orange,Peach;Strawberry,Mango,Grape;Raspberry,Kiwi,x
3
4	Apple	Orange	Peach
5	Strawberry	Mango	Grape
6	Raspberry	Kiwi
7
8
9
10	#VALUE!				{=Splitter3(A1,",",";")}
11
12
13
14	Apple				{=Splitter3(A2,",",";")}
15
16
17
18
split

Cell Formulas
Range	Formula
A10	A10	=Splitter3(A1,",",";")
E10,E14	E10	=FORMULATEXT(A10)
A14	A14	=Splitter3(A2,",",";")
Press CTRL+SHIFT+ENTER to enter array formulas.