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

haas67

New Member
Joined
Aug 17, 2022
Messages
14
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Mobile
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
what should the result look like?
 
Upvote 0
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.
vba-split.jpg
 
Upvote 0
It works fine for me. I used a 1 element string up to a 12 element string.

Book1
ABCDEFGHIJKLMNOPQRSTU
1 Apple,Orange,Peach,Strawberry,Mango,Grape,Raspberry,Kiwi,s,d,f,g
2Apple,Orange,Peach,Strawberry
3Apple
4Apple,Orange,Peach,Strawberry,Mango,Grape,Raspberry,Kiwi,x
5
6 AppleOrangePeachStrawberryMangoGrapeRaspberryKiwisdfg
7AppleOrangePeachStrawberry
8Apple
9AppleOrangePeachStrawberryMangoGrapeRaspberryKiwix
10
11
Sheet1
Cell Formulas
RangeFormula
J6:U6,J9:R9,J8,J7:M7J6=Splitter3(A1,",")
Dynamic array formulas.
 
Upvote 0
Did you actually try my mentioned failing string? The one in cell A1 from the picture
 
Upvote 0
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
ABCDEFGHIJKLMNOPQR
1Apple,Orange,Peach;Strawberry,Mango,Grape;Raspberry,Kiwi
2Apple,Orange,Peach,Strawberry
3Apple
4Apple,Orange,Peach,Strawberry,Mango,Grape,Raspberry,Kiwi,x
5
6AppleOrangePeach;StrawberryMangoGrape;RaspberryKiwi
7AppleOrangePeachStrawberry
8Apple
9AppleOrangePeachStrawberryMangoGrapeRaspberryKiwix
10
Sheet1
Cell Formulas
RangeFormula
J6:O6,J9:R9,J8,J7:M7J6=Splitter3(A1,",")
Dynamic array formulas.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Here is the mini-sheet.
Textjoin2016.xlsm
ABCDEFGH
1Apple,Orange,Peach;Strawberry,Mango,Grape;Raspberry,Kiwi
2Apple,Orange,Peach;Strawberry,Mango,Grape;Raspberry,Kiwi,x
3
4AppleOrangePeach
5StrawberryMangoGrape
6RaspberryKiwi
7
8
9
10#VALUE!{=Splitter3(A1,",",";")}
11
12
13
14AppleOrangePeach{=Splitter3(A2,",",";")}
15StrawberryMangoGrape
16RaspberryKiwix
17
Split
Cell Formulas
RangeFormula
A10A10=Splitter3(A1,",",";")
E10,E14E10=FORMULATEXT(A10)
A14:C16A14=Splitter3(A2,",",";")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
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
ABCDEF
1Apple,Orange,Peach;Strawberry,Mango,Grape;Raspberry,Kiwi
2Apple,Orange,Peach;Strawberry,Mango,Grape;Raspberry,Kiwi,x
3
4AppleOrangePeach
5StrawberryMangoGrape
6RaspberryKiwi
7
8
9
10#VALUE!{=Splitter3(A1,",",";")}
11
12
13
14Apple{=Splitter3(A2,",",";")}
15
16
17
18
split
Cell Formulas
RangeFormula
A10A10=Splitter3(A1,",",";")
E10,E14E10=FORMULATEXT(A10)
A14A14=Splitter3(A2,",",";")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top