TEXTSPLITVAR

[Jeffrey Mahoney]

TextSplitVar is a recursive formula to split a single cell or text array by the delimiter and return the variable chosen. It checks the number of variables and provides the last variable if the user asked for too large a number. It also adds one more delimiter at the end on the first iteration to provide a correct count and to find the correct variable easier, especially the last one. The premise is to remove the left most variable and provide the remaining string until n = Var.

As you can see in the Sample below, I asked for variable 11 and it returned the last item. I welcome any input to improve performance or to explain it better.

=LAMBDA(Txt,Del,Var,[n],IF(n=0,TextSplitVar(Txt&Del,Del,MIN((LEN(Txt&Del)-LEN(SUBSTITUTE(Txt&Del,Del,"")))/LEN(Del),Var),1),IF(n<Var,TextSplitVar(MID(Txt,SEARCH(Del,Txt)+LEN(Del),1000),Del,Var,n+1),MID(Txt,1,SEARCH(Del,Txt)-1))))

Excel Lambda TextSplitVar.xlsm
	A	B	C
2	Sample Text	Sample Formula	Lambda Formula with Variables Provided
3	Jeff, George, Frank, Apple, Lisa, Sammy	Sammy	Apple
4
5	Lambda Formula	Row 1 Row 2 Row 3 Row 4	=LAMBDA(Txt,Del,Var,[n], IF(n=0,TextSplitVar(Txt&Del,Del,MIN((LEN(Txt&Del)-LEN(SUBSTITUTE(Txt&Del,Del,"")))/LEN(Del),Var),1), IF(n<Var,TextSplitVar(MID(Txt,SEARCH(Del,Txt)+LEN(Del),1000),Del,Var,n+1), MID(Txt,1,SEARCH(Del,Txt)-1))))
6
7		Row 1	Txt = Cell with delimited text, Del = Delimter, Var = the variable you want to return, n = used internally to track current Var
8		Row 2	IF n = 0 then add one more delimiter at the end and change Var to max variables in Txt and Call TextSplitVar again Txt&Del = Add Del to end of Txt MIN((LEN(Txt&Del)-LEN(SUBSTITUTE(Txt&Del,Del,"")))/LEN(Del) = Counts the number of Del within Txt
9		Row 3	If N < Var then keep removing the variable at the beginning of the string and call TextSplitVar again MID(Txt,SEARCH(Del,Txt)+LEN(Del),1000) = Provide the remainder of the string without the first variable
10		Row 4	MID(Txt,1,SEARCH(Del,Txt)-1) = Provide the variable the user called and end the recursive Lambda
Sheet2

Cell Formulas
Range	Formula
B3	B3	=TextSplitVar(A3,", ",11)
C3	C3	=LAMBDA(Txt,Del,Var,[n],IF(n=0,TextSplitVar(Txt&Del,Del,MIN((LEN(Txt&Del)-LEN(SUBSTITUTE(Txt&Del,Del,"")))/LEN(Del),Var),1),IF(n<Var,TextSplitVar(MID(Txt,SEARCH(Del,Txt)+LEN(Del),1000),Del,Var,n+1),MID(Txt,1,SEARCH(Del,Txt)-1))))(A3,", ",4)

 

[lrobbo314]

I've been playing with making recursive lambdas as well. But, if we're talking about improving performance, I would think that something like below would be more efficient that doing it recursively.

EXCEL
	A	B	C
1	Sample Text	Example1	Example2
2	Jeff, George, Frank, Apple, Lisa, Sammy	Sammy	Apple
Sheet1

Cell Formulas
Range	Formula
B2	B2	=LAMBDA(text,delim,index,LET(s,TEXTSPLIT(text,,delim),IFERROR(INDEX(s,index),TAKE(s,-1))))(A2,", ",11)
C2	C2	=LAMBDA(text,delim,index,LET(s,TEXTSPLIT(text,,delim),IFERROR(INDEX(s,index),TAKE(s,-1))))(A2,", ",4)

For example, below is a lambda I wrote to extract numbers from a string. But, I think that the non-recursive versions probably perform better. As a bonus, the 'Non-Recursive2' has an optional argument that lets you toggle between extracting letters and numbers.

EXCEL
	A	B	C	D	E	F
1	Data		Recursive	Non-Recursive1	Non-Recursive2 (Number)	Non-Recursive2 (Text)
2	ABC123def456		123456	123456	123456	ABCdef
3	123abc		123	123	123	abc
Sheet4

Cell Formulas
Range	Formula
C2:C3	C2	=LAMBDA(text,LET(r,IF(ISERROR(SEARCH("Δ",text)),text&"Δ",text),lp,LEFT(r),rp,RIGHT(r,LEN(r)-1),IF(lp="Δ",rp+0,EXTRACTNUMREC(rp&IF(ISNUMBER(lp+0),lp,"")))))(A2)
D2:D3	D2	=LAMBDA(text,LET(n,LEN(text),s,SEQUENCE(n),m,MID(text,s,1),CONCAT(IF(ISNUMBER(m+0),m,""))))(A2)
E2:E3	E2	=LAMBDA(text,[numbers],TAKE(SCAN(text,SEQUENCE(LEN(text)),LAMBDA(a,b,RIGHT(a,LEN(a)-1)&IF(XOR(ISNUMBER(LEFT(a)+0),numbers),LEFT(a),""))),-1))(A2)
F2:F3	F2	=LAMBDA(text,[numbers],TAKE(SCAN(text,SEQUENCE(LEN(text)),LAMBDA(a,b,RIGHT(a,LEN(a)-1)&IF(XOR(ISNUMBER(LEFT(a)+0),numbers),LEFT(a),""))),-1))(A2,1)