How to made custom Split array function in MS Excel?

Question

How to make a custom made function Split(str as String, delimiter as String ) using Split() in VBA to be used in worksheet.. and it will return as array or SPILL the result.. For example,

Split("{20;30;40;50;60}",";")

will return Spill down Array result in the same array as in the written formula as:

I try to use the Split(String,";") in VBA function but it only return 1 value and in text type.. I also need to remove both ' { ' and ' } ' in the string if there but accept it even if it is no there..

You need to show your function line. I would bet that the error lays there. — Scott Craner, Dec 11 '20 at 20:27
Try this: `Application.Worksheets("YourWorksheet").Range("A1:A5") = Application.Transpose(Split(Replace(Replace("{20;30;40;50;60}", "{", ""), "}", ""), ";"))` — GWD, Dec 11 '20 at 20:30
@GWD if the OP has Office 365 all that is unnecessary and cannot be done with a UDF. — Scott Craner, Dec 11 '20 at 20:31
@GWD I am waiting for the OP to show theirs, so I can teach and not give the answer. — Scott Craner, Dec 11 '20 at 20:32

score 2 · Answer 1 · answered Dec 11 '20 at 20:34

The UDF should use a two dimensional array:

Public Function splitt(s As String, sep As String)
    arr = Split(s, sep)
    
    ReDim arr2(0 To UBound(arr), 1 To 1)
    
    For i = LBound(arr) To UBound(arr)
        arr2(i, 1) = arr(i)
    Next i
    
    splitt = arr2
End Function

Example:

With Excel 365 , it will auto-spill. Without Excel 365, it will need array-entry.

Scott Craner · Accepted Answer · 2020-12-11T20:43:26.593

2

Do not call your function Split
Use Nested Replace
We need to return a String array.

Function mysplit(str As String, deli As String) As String()
    mysplit = split(Replace(Replace(str, "{", ""), "}", ""), deli)
End Function

Then I would call it like this:

=LET(x,mySplit("{20;30;40;50;60}",";"),IF(ISNUMBER(--x),--x,x))

If you want it transposed then use TRANSPOSE

=LET(x,mySplit("{20;30;40;50;60}",";"),TRANSPOSE(IF(ISNUMBER(--x),--x,x)))

A single formula can do it all without the need of vba:

=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("{20;30;40;50;60}",";","</b><b>"),"}",""),"{","")&"</b></a>","//b")

edited Dec 11 '20 at 20:43

answered Dec 11 '20 at 20:37

Scott Craner

148,073
10
49
81

Thank you.. I am not good at VBA.. I try to search solution but cannot quite understand.. out of frustration I deleted the file.. I think I prefer the single formula without VBA.. and does FilterXML really need that a and b tag? What does that mean? – Mr. Tello Dec 12 '20 at 06:03
`FilterXM()`L needs a *wellformed* XML structure (comparable in some way with a html structure) with at least a `documentElement` *on top*, here e.g. `...` and additionally subnodes (e.g. `..`) therein to identify your array items :-) – T.M. Dec 12 '20 at 09:58

How to made custom Split array function in MS Excel?

2 Answers2