Creating a SPLIT function in Excel using lambdas

Excel Version
1. 365
A SPLIT function (to break delimited text into separate cells) would be very useful. It is easy to do in VBA, which has a built in function to do it, but I thought I'd try to do it with lambdas. I found it extremely difficult (not least because it is so hard to check recursive lambdas if you can't see intermediate results), but I learned a lot in achieving it. There may be a more efficient approach but I thought it was worth sharing what I learned.

First, the final lambda function, assumed to be named SPLITTER - you can name it something else, but then replace the SPLITTER reference in the formula with your name)
Excel Formula:
``=LAMBDA(txt,delim,[n],[list], LET(p,IFERROR(FIND(delim,txt),0), list,IF(ISOMITTED(list), {""}, list), IF(p,Splitter(MID(txt,p+1,99),delim,n+1, IF(SEQUENCE(1,n+1)<n+1,list,LEFT(txt,p-1))),IF(n=0,txt,IF(SEQUENCE(1,n+1)<n+1,list,txt)) )))``
Usage: = SPLIT(String_to_split, Delimiter)
eg = SPLIT("a,b,c,d", ",")

Notes:
It works using recursion, extracting the first item and putting it in a list, then calling itself to extract the next item, and so on. The process is like this -
1. inputs are the text to split, delimiter, number of items found so far, the list of those items (the last two items are not provided by the user initially but are used for recursion, so they are marked optional)
2. find the position of the next delimiter and store in p
3. if the list variable is blank (this will be initially only), set it to an array of a single blank
4. if we found a delimiter, call SPLIT again with the remaining text, delimiter, number of items+1, and append the latest item to list
5. if we didn't find a delimiter, we are done, so append the remaining text to list and output it
Creating a variable length list
It took me a long time to figure how to create a variable length list to which I could append each item as I pulled it off. The SEQUENCE function came to the rescue.
This will take an existing list L of length n and append a new item T on the end, so the array now has n+1 items
Excel Formula:
``IF(SEQUENCE(1,n+1)<n+1, L, T)``

Redefining lambda inputs
The list of items found so far is passed to SPLIT for the next recursion - but the first time round, there is no list to append to, so the LET checks this, and creates a blank array. The lesson for me was that you can take a lambda input and redefine it if you need to
Excel Formula:
``IF(ISOMITTED(list), {""}, list)``
Tetonne
Author
Dermot
Views
3,778
First release
Last update
Rating
2 ratings

Latest reviews

Thanks for posting this recursive Lambda!

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.

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

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