# question

## Difficult problem in lambda formula.

Help, lambda formula ? image.png (10.8 KiB)
· 2

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@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"?

1 Vote 1 ·

Yes, you're right. B3 = “ a，b，k，c，e1，e2，e3，e4，e5，e6，e7，e8，e9，f3”

@CanU-7982
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.

11.png (52.0 KiB)
· 1

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Very appreciated for your supports! Let me have a deeper understanding of LAMBDA. English is not my native language; please excuse typing errors.

@CanU-7982

A Get & Transform aka Power Query solution. With your data in Table1 > Right click on the green table > Refresh Corresponding workbook avail. here

demo.png (58.8 KiB)
· 2

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Greatly appreciate any help!

Glad I could help @CanU-7982. Wish I had LAMBA to give this one a try

1 Vote 1 ·

@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.

1. 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.

2. 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.

3. parse: this was purpose-built to expand "x1-3" strings to x1,x2,x3.

4. 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.

lambda-expand.png (22.4 KiB)

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.