Help, lambda formula ?

- Home
- Anonymous
- Sign in to post
- Post
- Explore
- Tags
- Questions
- Site feedback
- Articles
- Users

Help, lambda formula ?

image.png
(10.8 KiB)

Comment

·
2

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

Yes, you're right. B3 = “ 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.

11.png
(52.0 KiB)

·
1

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

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

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

lambda-expand.png
(22.4 KiB)

0

**6** people are following this question.

- About
- ·
- FAQ
- ·
- Privacy
- ·
- Copyright © 2010-22 DZone, Inc.