Help, lambda formula ? 
Difficult problem in lambda formula.
@CanU-7982
I am confused that whether the string of B2 is correct. Or is it should be "a,b,k,c,e1,e2,e3,e4,e5,e6,e7,e8,e9,f3"?
@CanU-7982
Thanks for your explaination.
Based on my research, maybe you need the code to solve your problems efficiently.
Currently, I can use formula to expand a string with a range. If there are multiple ranges, I think you need to split it first.
I use 7 Lambda formulas to get the string that you need. I personally find it more complicated to use formulas.
Part1,
=LAMBDA(x,LOOKUP(9^9,1*RIGHT(MID(x, FIND("-",x)-4,4),ROW($1:$4))))Part2,
=LAMBDA(y,LOOKUP(9^9,1*LEFT(MID(y, FIND("-",y),4),ROW($1:$4))))Part3,
=LAMBDA(a,MID(a,FIND((Part1(a)),a)-1,1))Part4,
=LAMBDA(x,Part3(x)&TEXTJOIN(","&Part3(x),,ROW(INDIRECT(SUBSTITUTE(MID(x,FIND(Part1(x),x)-1,1)&Part1(x)&Part2(x),"-",":"&Part3(x))))))Part5,
=LAMBDA(z,LEFT(z,FIND(Part3(z)&Part1(z),z)-1))Part6,
=LAMBDA(x,RIGHT(x,(LEN(x)-FIND(Part2(x),x)-LEN(Part2(x))+1)))Part7,
=LAMBDA(x,y,z,(x&y&z))
To better help you, I suggest you add "office-vba-dev", check whether you could get the solution of VBA code.
Thanks for your understanding.
If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Very appreciated for your supports! Let me have a deeper understanding of LAMBDA. English is not my native language; please excuse typing errors.
A Get & Transform aka Power Query solution. With your data in Table1 > Right click on the green table > Refresh
Corresponding workbook avail. here
@CanU-7982, give this a try; it is made of 4 individual Let-based LAMBDAs. It's longer, but I think it helps explain what each step does with LET.
TextToArray: this is a LAMBDA I built for general application, it breaks a string on a delimiter, or if delimiter is blank, splits on every char.
ListCombine: this is a LAMBDA I built to combine lists; it is used in the recursive loop to pass results back up the stack to the main function.
parse: this was purpose-built to expand "x1-3" strings to x1,x2,x3.
Loop: this recursive LAMBDA mimics a For-loop.
You can easily define 1-3 in the Name Manager to shorten it; because 4 is recursive, it will require some adjustment.
=LAMBDA(txt,
LET(
TextToArray,
LAMBDA(str,del,
LET(
dsd,del&str&del,
posArray,
IF(
ISBLANK(del),SEQUENCE(LEN(str)+1),
FIND(CHAR(1),SUBSTITUTE(dsd,del,CHAR(1),SEQUENCE((LEN(dsd)-LEN(SUBSTITUTE(dsd,del,"")))/LEN(del))))
)-1,
startPosArray,INDEX(posArray,SEQUENCE(ROWS(posArray)-1)),
splitPosArray,INDEX(posArray,SEQUENCE(ROWS(posArray)-1,1,2,1)),
charArray,MID(str,startPosArray+1,splitPosArray-startPosArray-LEN(del)),
valArray,IFERROR(VALUE(charArray),charArray),
valArray
)
),
ListCombine,
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)
)
)
),
parse,
LAMBDA(str,
IF(
ISERROR(FIND("-",str)),str,
LET(
strArr,TextToArray(str,),
strBeg,INDEX(strArr,SEQUENCE(MATCH("-",strArr,0)-1)),
strEnd,INDEX(strArr,SEQUENCE(ROWS(strArr)-MATCH("-",strArr,0))+MATCH("-",strArr,0)),
isNumBeg,ISNUMBER(strBeg),
isNumEnd,ISNUMBER(strEnd),
strBegTxt,TEXTJOIN("",TRUE,FILTER(strBeg,NOT(isNumBeg),"")),
strBegNum,VALUE(TEXTJOIN("",TRUE,FILTER(strBeg,isNumBeg,""))),
strEndTxt,TEXTJOIN("",TRUE,FILTER(strEnd,NOT(isNumEnd),"")),
strEndNum,VALUE(TEXTJOIN("",TRUE,FILTER(strEnd,isNumEnd,""))),
strBegTxt&SEQUENCE(strEndNum-strBegNum+1,1,strBegNum,1)
)
)
),
txtArray,TextToArray(txt,","),
stop,ROWS(txtArray),
Loop,
LAMBDA(ME,i,stopCt,func,
LET(
input,ARRAYTOTEXT(INDEX(txtArray,i)),
value,func(input),
return,
IF(
i<stopCt,ME(ME,i+1,stopCt,func),
value
),
IF(
i=stopCt,return,
ListCombine(value,return)
)
)
),
TEXTJOIN(",",TRUE,Loop(Loop,1,stop,parse))
)
)
This handles some more general cases as well.
6 people are following this question.
