TEXTSPLIT

=TEXTSPLIT(rangeToSplit,separator,nthOccurrence)

rangeToSplit
range containing text values to be split (horizontal or vertical, single column)
separator
single text value of any length to split after each occurrence
nthOccurrence
0 to return all splits, otherwise integer or array of integers (1 or {2,4} or {2,2} etc)

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

Board Regular
Joined
May 1, 2019
Messages
62
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)

Excel Formula:
=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
BCDEFGHI
2Split function, tested on horizontal arraySplit function, tested on vertical array
3
4test;|of;|split;|this;|testtexas;|of;|splittreat;|me;|with;|care;|please;|peopletest;|of;|split;|this;|test|of|split|this
5testtexastreattext;|of;|split|of|split
6ofofmeteat;|of;|split;make;this;final;;|of|splitmake
7splitsplitwithtent;|of;|split|of|split
8thiscaretelt;|of;|split|of|split
9testpleaseteft;|of;|split|of|split
10peopletert;|of;|split|of|split
11teit;|of;|split|of|split
12
Sheet2
Cell Formulas
RangeFormula
G4:I11G4=TEXTSPLIT(F4:F11,";",{2,3,4})
B5:D10B5=TEXTSPLIT(B4:D4,";|",0)
Dynamic array formulas.
 
Last edited by a moderator:
Upvote 0
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.
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".
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.
 
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):

Excel Formula:
=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.xlsx
BCDEFGHIJKL
1Original Array
2123
3abc
4@£$
5
6
7Vertical listVrtcl lst1, hrzntl lst2Horizontal list
811123abc
9aa
10@@Horizontal list1, vertical list2
113a1232b£
12cb
13$c
14
Sheet1
Cell Formulas
RangeFormula
B8: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.
 
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".
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.
 
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!).
 

Forum statistics

Threads
1,223,575
Messages
6,173,148
Members
452,502
Latest member
PQCurious

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