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.

 

[tboulden]

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.

 

[smozgur]

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]

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.xlsx
	B	C	D	E	F	G	H	I	J	K	L
1	Original Array
2	1	2	3
3	a	b	c
4	@	£	$
5
6
7	Vertical list		Vrtcl lst1, hrzntl lst2		Horizontal list
8	1		1		1	2	3	a	b	c
9	a		a
10	@		@		Horizontal list1, vertical list2
11	3		a		1	2	3	2	b	£
12	c		b
13	$		c
14
Sheet1

Cell Formulas
Range	Formula
B8:B13	B8	=LISTCOMBINE(B2:B4,D2:D4)
D8:D13	D8	=LISTCOMBINE(B2:B4,B3:D3)
F8:K8	F8	=LISTCOMBINE(B2:D2,B3:D3)
F11:K11	F11	=LISTCOMBINE(B2:D2,C2:C4)
Dynamic array formulas.

 

[tboulden]

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.

 

[RicoS]

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