- Excel Version
- 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)
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 -
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
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
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)) )))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 -
- 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)
- find the position of the next delimiter and store in p
- if the list variable is blank (this will be initially only), set it to an array of a single blank
- if we found a delimiter, call SPLIT again with the remaining text, delimiter, number of items+1, and append the latest item to list
- if we didn't find a delimiter, we are done, so append the remaining text to list and output it
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)
