Difficult problem in lambda formula.

Question

Help, lambda formula ?

Answer 1

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

Answer 2

@Can.U

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

Corresponding workbook avail. here

Answer 3

@Can.U , 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.