question

CanU-7982 avatar image
0 Votes"
CanU-7982 asked TB-2770 answered

Difficult problem in lambda formula.

Help, lambda formula ?
76467-image.png


office-excel-itpro
image.png (10.8 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

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”

0 Votes 0 ·
emilyhua-msft avatar image
0 Votes"
emilyhua-msft answered CanU-7982 commented

@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.
77204-11.png

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


11.png (52.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

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.

0 Votes 0 ·
Lz-3068 avatar image
0 Votes"
Lz-3068 answered Lz-3068 commented

@CanU-7982

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

77155-demo.png

Corresponding workbook avail. here



demo.png (58.8 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

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!

0 Votes 0 ·

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

1 Vote 1 ·
TB-2770 avatar image
0 Votes"
TB-2770 answered

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

79338-lambda-expand.png
This handles some more general cases as well.



lambda-expand.png (22.4 KiB)
5 |1600 characters needed characters left characters exceeded

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