TEXTSPLIT

[RicoS]

TEXTSPLIT splits an array of text values by a specified delimiter (like text to columns, or vba split function), allowing the user to select Nth split occurrence(s)

=LAMBDA(textToSplit,separator,nthOccurrence,
     LET(tToS,textToSplit,
        hrzntl,(ROWS(tToS)=1)*(COLUMNS(tToS)>1),
        t,IF(hrzntl=1,TRANSPOSE(tToS),tToS),
        r,ROWS(t),
        s,separator,
        seqR,SEQUENCE(r),
        tLn,LEN(t),
        sLn,LEN(s),
        maxTLn,MAX(tLn),
        noOfFinds,(tLn-LEN(SUBSTITUTE(t,s,"")))/sLn,maxF,MAX(noOfFinds),
        seqT,SEQUENCE(,maxTLn),
        finds,IF(seqT>tLn,0,seqT*(MID(t,seqT,sLn)=s)),
        cntFinds,COUNTA(finds),
        seqFinds,SEQUENCE(cntFinds,1,0),
        rF,INT(seqFinds/maxTLn)+1,
        finds_1d,INDEX(finds,rF,MOD(seqFinds,maxTLn)+1),
        finds_1dSrt,SORTBY(finds_1d,rF&TEXT(IF(finds_1d=0,maxTLn+1,finds_1d),"0000000")),
        finds_2d,INDEX(finds_1dSrt,maxTLn*(seqR-1)+SEQUENCE(,maxTLn),1),
        seqN,SEQUENCE(,maxF+1,0),
        starts,IF(seqN=0,1,INDEX(finds_2d,seqR,seqN)),
        startPos,IF(starts<2,starts,starts+sLn),
        ends,IF(seqN=noOfFinds,tLn+1,INDEX(finds_2d,seqR,seqN+1)),
        splitArray,IF(startPos=0,"",MID(t,startPos,ends-startPos)),
        nthCols,COLUMNS(nthOccurrence),
        nC,IF(nthCols=1,IF(nthOccurrence=0,maxF+1,1),nthCols),
        splitNth,IF(nthOccurrence=0,splitArray,INDEX(splitArray,seqR,nthOccurrence)),
        indexNth,INDEX(splitNth,seqR,SEQUENCE(,nC)),
        return,IF(hrzntl=1,TRANSPOSE(indexNth),indexNth),
        return
     )
)

Lambda - Last Cell and Split.xlsx
	B	C	D	E	F	G	H	I
2	Split function, tested on horizontal array				Split function, tested on vertical array
3
4	test;|of;|split;|this;|test	texas;|of;|split	treat;|me;|with;|care;|please;|people		test;|of;|split;|this;|test	|of	|split	|this
5	test	texas	treat		text;|of;|split	|of	|split
6	of	of	me		teat;|of;|split;make;this;final;;	|of	|split	make
7	split	split	with		tent;|of;|split	|of	|split
8	this		care		telt;|of;|split	|of	|split
9	test		please		teft;|of;|split	|of	|split
10			people		tert;|of;|split	|of	|split
11					teit;|of;|split	|of	|split
12
Sheet2

Cell Formulas
Range	Formula
G4:I11	G4	=TEXTSPLIT(F4:F11,";",{2,3,4})
B5:D10	B5	=TEXTSPLIT(B4:D4,";|",0)
Dynamic array formulas.

 

[Eric W]

I don't yet have the LAMBDA function, so I've been unable to do much with it except theoretically. Using the recursive ability of LAMBDA, I designed a TEXTSPLIT function. I believe the functionality is similar to yours, although I haven't been able to test either. But for future reference, here's what I came up with:

=LAMBDA(textToSplit,separator,NthOccurence,
        IF(OR(textToSplit="",NthOccurence<2),
           LEFT(textToSplit,SEARCH(separator,textToSplit&separator)-1),
           TEXTSPLIT(MID(textToSplit,SEARCH(separator,textToSplit&separator)+LEN(separator),LEN(textToSplit)),separator,NthOccurence-1)
          )
       )

 

[RicoS]

I actually need to tidy mine up a little, as I should have used multiple LAMBDAS to produce mine, rather than an iterative LET function (seeing as Functional seems to be the trend!), I produced mine as just a LET before LAMBDAs came about (so you can probably test it too!).

Anyway, I gave yours a try, and I'm struggling to get it to work in the way mine does? Does it survive the dynamic array test? I appreciate that's exceptionally difficult for you to tell given you don't have Lambdas yet!! Basically, what I wanted was something that returned a two dimensional array rather than just a single occurrence of the word, so you could return occurrences 1 and 2, 2 and 4 or all occurrences within the function (range B5:D10 is populated with a single formula in B5 in my example). It is more like TEXTTOCOLUMNS, which might have been a better name! When I use yours, I can only return the nth. What is supposed to be entered as NthOccurrence in your function if I want to return all to an array? Again, I know your sailing into the wind without the functionality, so apologies if I'm not being clear.

 

[Eric W]

Yes, you've hit on several of the differences I noticed, that's why I said "similar" functionality. I saw that your function can return a 2-d array based on your example. That's not something I envisaged. The base idea of mine was to return a single instance, as you noticed. But I believe that you can create a 1-d array by using the SPILL functionality. Try calling it like:

=TEXTSPLIT("this is a test"," ",{2,4})

or

=TEXTSPLIT("this is a test"," ",SEQUENCE(4))

and see what happens. You added the option of giving a 0 for NthOccurence to get all instances, which I thought was a nice touch. With mine it would be something like:

=TEXTSPLIT(A5,",",SEQUENCE(9999))

where the 9999 is some very big number, and the recursion would end when the entire input textToSplit string had been consumed. Actually, that might not be such a good idea. It would create an output array of 9999 entries, most of which are empty. Maybe:

=TEXTSPLIT(A5,",",SEQUENCE((LEN(A5)-LEN(SUBSTITUTE(A5,",",""))/LEN(",")+1)

but that's really awkward.

I've also thought about incorporating the "treat consecutive separators as one" functionality from the Text to Columns tool, but haven't figured out how to do that. Probably a second recursive LAMBDA would be required. Or a trick with TRIM.

In any case, thank you for looking at this!

 

[RicoS]

Yes, I tried typing in the sequence and array options initially, but it didn't work. I think the issue is that there's not a "way in" for the function when you pass an array like that. You can obviously perform the NthOccurrence<2 and NthOccurrence-1 calculations on an array, but that's not really how the function is set up, so it doesn't know which sequence of the array it is referencing, nor which to recurse forward. It simply takes the lowest value of the array, which is curious (so {4,2}, {2,3} return "is")! I suppose you realy have to think in a 2D world with dynamic arrays and it is difficult to conceptualise.

I should add that I've not had great success with Recursion so far, it seems a little volatile. I think it is perhaps a memory thing, but the number of recursions are limited and it seems less optimal than just generic LET-Based-LAMBDAs (LBLs...). Other than the two functions that are in the my other threads on this board (LASTCOLUMN and LASTROW), I haven't found much that needed recursion yet. I've had greater success with just using LAMBDAS with LET and have created lots of reusable ones (I'll put them on here soon) I think that deal with arrays in the way that we probably haven't needed to before. I suppose it retains the nested function of traditional excel and gets you thinking in ones and zeros. The thing is that if you're creating a LAMBDA anyway, there is likely little need to simplify verbose code in a recursion, as you'll only ever call the LAMBDA. As I mentioned, I need to tidy up my LET from above, as I can write that function in a handful of lines, with specific LAMBDAS for each of the steps (I should have done that in the first place). I'll try and update in the next few days.

To be honest, I'm just glad someone else is looking at the functions I posted! Very good to hear from you!

 

[Eric W]

Well, that's disappointing! I guess the combination of LAMBDA and array processing doesn't work the way I expected/hoped. You could still do something like:

=TEXTSPLIT($A$5,",",COLUMNS($B5:B5))

and drag it to the right, but that seems counter to the current direction of Excel. I guess I'll have to wait until I get LAMBDA so I can do some actual testing. You seem to have done a fair amount along those lines and have learned a bit.

I've looked at your other functions, and I can't test them yet, but they look promising. Thanks again for helping me out!

 

[RicoS]

No problem at all! I will put up some more in the coming days for people to improve on.

 

[tboulden]

I think the issue is that there's not a "way in" for the function when you pass an array like that

I think this is correct, but I'm fairly new to this as well. As you recurse, the value exists at that depth in the stack call; you have to build your function in such a way that you can accumulate the results for each level and return the full accumulation when you get back up to the level of the first call. Effectively, we want to use the recursive property of LAMBDA to mimic a FOR loop; using dynamic arrays allows for a way to do this as well, and its almost certainly more efficient since there are limits on the stack depth for recursion.

I guess the combination of LAMBDA and array processing doesn't work the way I expected/hoped

I think I can put together a modification of your LAMBDA that will work in this FOR loop manner. Since textToSplit doesn't change through our operations, we're iterating through the positions of the separator based on i = 1 to count(separator) = n. So we just need a helper function that determines the split-string for the current i, and then our LAMBDA recursively calls itself for the next i until we get to n. When we get to the last separator through the end of textToSplit, we're at the bottom of the stack, and can return the result from our helper function as an array of length 1 with the string. Passing the array back up the stack, we add the result of the helper function at each level to the array, and then at the top-level the whole array should spill as expected. I'll give it a try tomorrow!

 

[tboulden]

I think I can put together a modification of your LAMBDA that will work in this FOR loop manner.

And here's what the monster I came up with looks like:

=LAMBDA(                                        
    txt,                                 
    separator,                                 
    func,                                 
    LET(                                    
        Combine, LAMBDA(                            
                        list_1,                     
                        list_2,                     
                            LET(                        
                                i, COUNTA(list_1),             
                                j, COUNTA(list_2),             
                                k, SEQUENCE(i+j),             
                                IF(                    
                                    k<=i, INDEX(list_1,k),         
                                    INDEX(list_2,k-i)                
                                )                    
                            )                        
                    ),                         
        Loop,     LAMBDA(                            
                            ME,                     
                            txtToSplit,                     
                            LET(                        
                                value, func(txtToSplit,separator),             
                                return, IF(                
                                            txtToSplit<>value, ME(        
                                                                    ME, 
                                                                    RIGHT(    
                                                                        txtToSplit,
                                                                        LEN(txtToSplit)
                                                                        -
                                                                        SEARCH(separator,txtToSplit)
                                                                    )    
                                                                ),     
                                            value            
                                        ),             
                                IF(                    
                                    txtToSplit=value, return,         
                                    Combine(value,return)                
                                )                    
                            )                        
                ),                         
        Loop(Loop,txt)                                
    )                                    
)

LAMBDA_Testing_TEXTSPLIT.xlsm
	A	B	C	D
7	Parameter	Value		Output
8	txt	This#is#a#test		This
9	separator	#		is
10	func	LAMBDA(str,sep,IFERROR(MID(str,1,SEARCH(sep,str)-1),str))		a
11				test
12
13	txt	I think I can put together a modification of your LAMBDA that will work in this FOR loop manner.		I
14				think
15				I
16				can
17	separator			put
18	func	LAMBDA(str,sep,IFERROR(MID(str,1,SEARCH(sep,str)-1),str))		together
19				a
20				modification
21				of
22				your
23				LAMBDA
24				that
25				will
26				work
27				in
28				this
29				FOR
30				loop
31				manner.
TEXTSPLIT

Cell Formulas
Range	Formula
D8:D11	D8	=LAMBDA(txt,separator,func,LET(Combine,LAMBDA(list_1,list_2,LET(i,COUNTA(list_1),j,COUNTA(list_2),k,SEQUENCE(i+j),IF(k<=i,INDEX(list_1,k),INDEX(list_2,k-i)))),Loop,LAMBDA(ME,txtToSplit,LET(value,func(txtToSplit,separator),return,IF(txtToSplit<>value,ME(ME,RIGHT(txtToSplit,LEN(txtToSplit)-SEARCH(separator,txtToSplit))),value),IF(txtToSplit=value,return,Combine(value,return)))),Loop(Loop,txt)))(B8,B9,LAMBDA(str,sep,IFERROR(MID(str,1,SEARCH(sep,str)-1),str)))
D13:D31	D13	=TextSplit(B13,B17,LAMBDA(str,sep,IFERROR(MID(str,1,SEARCH(sep,str)-1),str)))
Dynamic array formulas.

 

[tboulden]

        Combine, LAMBDA(                           
                        list_1,                    
                        list_2,                    
                            LET(                       
                                i, COUNTA(list_1),            
                                j, COUNTA(list_2),            
                                k, SEQUENCE(i+j),            
                                IF(                   
                                    k<=i, INDEX(list_1,k),        
                                    INDEX(list_2,k-i)               
                                )                   
                            )                       
                    )

Note that this is included in the body of my example, however you could give it a name in the Name Manager and reference it that way; this is my beginner's version of List.Combine we know and love from Power Query.