TEXTSPLITVAR

=TEXTSPLITVAR(Txt,Del,Var)
Txt
Cell with string or String
Del
String with one or more characters
Var
whole number to choose which variable you want to return
[n]
No need to provide; used internally for current variable
Return one variable from a string with items separated by a delimiter of one or more characters
Jeffrey Mahoney

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
2,856
Office Version
  1. 365
Platform
  1. Windows
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.



Excel Formula: 
=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
ABC
2Sample TextSample FormulaLambda Formula with Variables Provided
3Jeff, George, Frank, Apple, Lisa, SammySammyApple
4
5Lambda FormulaRow 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
7Row 1Txt = Cell with delimited text, Del = Delimter, Var = the variable you want to return, n = used internally to track current Var
8Row 2IF 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
9Row 3If 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
10Row 4MID(Txt,1,SEARCH(Del,Txt)-1) = Provide the variable the user called and end the recursive Lambda
Sheet2
Cell Formulas
RangeFormula
B3B3=TextSplitVar(A3,", ",11)
C3C3=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)
 
Upvote 0
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
ABC
1Sample TextExample1Example2
2Jeff, George, Frank, Apple, Lisa, SammySammyApple
Sheet1
Cell Formulas
RangeFormula
B2B2=LAMBDA(text,delim,index,LET(s,TEXTSPLIT(text,,delim),IFERROR(INDEX(s,index),TAKE(s,-1))))(A2,", ",11)
C2C2=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
ABCDEF
1DataRecursiveNon-Recursive1Non-Recursive2 (Number)Non-Recursive2 (Text)
2ABC123def456123456123456123456ABCdef
3123abc123123123abc
Sheet4
Cell Formulas
RangeFormula
C2:C3C2=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:D3D2=LAMBDA(text,LET(n,LEN(text),s,SEQUENCE(n),m,MID(text,s,1),CONCAT(IF(ISNUMBER(m+0),m,""))))(A2)
E2:E3E2=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:F3F2=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)
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

Share this page

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