# TEXTSPLIT  	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).



## RicoS (Feb 16, 2021)

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.xlsxBCDEFGHI2Split function, tested on horizontal arraySplit function, tested on vertical array34test;|of;|split;|this;|testtexas;|of;|splittreat;|me;|with;|care;|please;|peopletest;|of;|split;|this;|test|of|split|this5testtexastreattext;|of;|split|of|split6ofofmeteat;|of;|split;make;this;final;;|of|splitmake7splitsplitwithtent;|of;|split|of|split8thiscaretelt;|of;|split|of|split9testpleaseteft;|of;|split|of|split10peopletert;|of;|split|of|split11teit;|of;|split|of|split12Sheet2Cell FormulasRangeFormulaG4:I11G4=TEXTSPLIT(F4:F11,";",{2,3,4})B5:D10B5=TEXTSPLIT(B4:D4,";|",0)Dynamic array formulas.


----------



## Eric W (Mar 3, 2021)

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 (Mar 3, 2021)

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 (Mar 3, 2021)

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 (Mar 3, 2021)

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 (Mar 3, 2021)

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 (Mar 3, 2021)

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


----------



## tboulden (Mar 3, 2021)

RicoS said:


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



Eric W said:


> 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 (Mar 4, 2021)

tboulden said:


> 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.xlsmABCD7ParameterValueOutput8txtThis#is#a#testThis9separator#is10funcLAMBDA(str,sep,IFERROR(MID(str,1,SEARCH(sep,str)-1),str))a11test1213txtI think I can put together a modification of your LAMBDA that will work in this FOR loop manner.I14think15I16can17separator put18funcLAMBDA(str,sep,IFERROR(MID(str,1,SEARCH(sep,str)-1),str))together19a20modification21of22your23LAMBDA24that25will26work27in28this29FOR30loop31manner.TEXTSPLITCell FormulasRangeFormulaD8:D11D8=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:D31D13=TextSplit(B13,B17,LAMBDA(str,sep,IFERROR(MID(str,1,SEARCH(sep,str)-1),str)))Dynamic array formulas.


----------



## tboulden (Mar 4, 2021)

tboulden said:


> ```
> Combine, LAMBDA(
> list_1,
> list_2,
> ...


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.


----------



## RicoS (Feb 16, 2021)

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.xlsxBCDEFGHI2Split function, tested on horizontal arraySplit function, tested on vertical array34test;|of;|split;|this;|testtexas;|of;|splittreat;|me;|with;|care;|please;|peopletest;|of;|split;|this;|test|of|split|this5testtexastreattext;|of;|split|of|split6ofofmeteat;|of;|split;make;this;final;;|of|splitmake7splitsplitwithtent;|of;|split|of|split8thiscaretelt;|of;|split|of|split9testpleaseteft;|of;|split|of|split10peopletert;|of;|split|of|split11teit;|of;|split|of|split12Sheet2Cell FormulasRangeFormulaG4:I11G4=TEXTSPLIT(F4:F11,";",{2,3,4})B5:D10B5=TEXTSPLIT(B4:D4,";|",0)Dynamic array formulas.


----------



## tboulden (Mar 4, 2021)

tboulden said:


> So we just need a helper function that determines the split-string for the current i,


This is passed in as a parameter "func" to my LAMBDA.


tboulden said:


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


This is outlined in "Loop".


tboulden said:


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


Combine accumulates "value" at each level of the stack into a final list that is returned.


----------



## smozgur (Mar 4, 2021)

I think it is better to mention about ME concept in a Lambda function for the readers.

A very good article about it:
LAMBDA Formulaic Recursion: It’s All About ME!


----------



## RicoS (Mar 5, 2021)

tboulden said:


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



You should add that as a new function in this board. I came up with something similar called combine arrays for combining arrays and I was just using that for both 1D and 2D arrays. It never even occurred to me that the word "List" is a far better description to differentiate between 1D and 2D and to replicate the logic of the function in Power Query. Great idea. The only change I'd make to yours is to determine if it's a horizontal of vertical list first, as I'm guessing that as a function you would want to combine in the same manner the data was presented? This would do the trick, using the vertical or horizontal status of list_1 to determine the direction of output (in my combinearrays() function, I used a third criteria for direction because the arrays were 2D):


```
=LAMBDA(list_1,list_2,
   LET(
      isHrzntl,COLUMNS(list_1)>ROWS(list_1),
      lst_1,IF(isHrzntl,TRANSPOSE(list_1),list_1),
      isHrzntl_2,COLUMNS(list_2)>ROWS(list_2),
      lst_2,IF(isHrzntl_2,TRANSPOSE(list_2),list_2),
      i,ROWS(lst_1),
      j,ROWS(lst_2),
      k,SEQUENCE(i+j),
      indx,IF(k<=i,INDEX(lst_1,k),INDEX(lst_2,k-i)),
      return,IF(isHrzntl,TRANSPOSE(indx),indx),
   return)
)
```
 
Lambda - Last Cell and Split.xlsxBCDEFGHIJKL1Original Array21233abc4@£$567Vertical listVrtcl lst1, hrzntl lst2Horizontal list811123abc9aa10@@Horizontal list1, vertical list2113a1232b£12cb13$c14Sheet1Cell FormulasRangeFormulaB8:B13B8=LISTCOMBINE(B2:B4,D2:D4)D8:D13D8=LISTCOMBINE(B2:B4,B3:D3)F8:K8F8=LISTCOMBINE(B2:D2,B3:D3)F11:K11F11=LISTCOMBINE(B2:D2,C2:C4)Dynamic array formulas.


----------



## tboulden (Mar 5, 2021)

smozgur said:


> I think it is better to mention about ME concept in a Lambda function for the readers.
> 
> A very good article about it:
> LAMBDA Formulaic Recursion: It’s All About ME!


That's a very useful link, and I picked up the habit of using ME from there, but we could call it anything we like: "self" I think would be informative, maybe more so than "ME".


RicoS said:


> You should add that as a new function in this board. I came up with something similar called combine arrays for combining arrays and I was just using that for both 1D and 2D arrays. It never even occurred to me that the word "List" is a far better description to differentiate between 1D and 2D and to replicate the logic of the function in Power Query. Great idea. The only change I'd make to yours is to determine if it's a horizontal of vertical list first, as I'm guessing that as a function you would want to combine in the same manner the data was presented?


I've been learning Power Query and noticed the "let .... in ...." structure is very similar to what we do with the LET() function, so I briefly thought about trying to recreate a bunch of PQ functions with LET/LAMBDA; obviously I haven't gotten very far  It might be a good strategy for uniformity to try and make LAMBDAs of PQ functions as similar as possible to the functionality of the actual Power Query function, and that's why I haven't added it as List.Combine, but I will add it with a similar name and provide the same disclaimer there as here.


----------



## RicoS (Mar 5, 2021)

tboulden said:


> I've been learning Power Query and noticed the "let .... in ...." structure is very similar to what we do with the LET() function, so I briefly thought about trying to recreate a bunch of PQ functions with LET/LAMBDA; obviously I haven't gotten very far  It might be a good strategy for uniformity to try and make LAMBDAs of PQ functions as similar as possible to the functionality of the actual Power Query function, and that's why I haven't added it as List.Combine, but I will add it with a similar name and provide the same disclaimer there as here.


Great idea. I put up my COMBINEARRAYS version earlier, but despite me complementing your nod to power query with list.combine, I didn't think to call it table.combine, which it is basically the same as. Maybe the folks at Excel will pick up all these Lambdas and make built-in functions out of them and they can sort out the names (and then sell them back to us at an increased subscription fee!).


----------

