SPLITTER

=SPLITTER(TextToSplit, Delimiter)

TestToSplit
a text string
Delimiter
a character(s) used to split the string into pieces

SPLITTER splits a text string into pieces based on a delimiter, using a recursive lambda

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
199
Office Version
  1. 365
Platform
  1. Windows
SPLITTER splits a text string into pieces based on a delimiter, using a recursive lambda

It is not as brief as SPLITONDELIMITER, but is an example of recursion, and in particular, shows how to store an increasing list of items from one recursion to the next.
Specifically, this
Excel Formula:
IF(SEQUENCE(1,n+1)<n+1, L, T)
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.
This could be very useful in recursive lambdas which need to store variable length lists.

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)))))

It starts by finding the next delimiter position (set to 0 if absent), and initialises the list of split items if missing.
Then if there is a delimiter, it calls splitter again, passing the string (minus the first item) and the array of items (adding the one just found)
If there is no delimiter left, it adds the last piece of text to the list and returns it as a row of items
 
Upvote 0
This is my most used Lambda since discovering it weeks ago.
 

Forum statistics

Threads
1,224,829
Messages
6,181,219
Members
453,024
Latest member
Wingit77

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