英文:
How can I generate combinations from given string value in google sheet?
问题
我有单元格值www_2_w4_14，我想要获取所有组合，例如www、www_2、www_2_w4、www_2_w4_14。我尝试了SPLIT、ARRAYFORMULA、JOIN、SUBSTITUTE等函数的组合，但似乎对我不起作用。
我尝试了以下公式，但目前还没有成功。
1> =LEFT(A2, FIND("@", SUBSTITUTE(A2, "", "@", LEN(A2) - LEN(SUBSTITUTE(A2, "", ""))))
2> =ArrayFormula(JOIN(",", ArrayFormula(LEFT(SPLIT(A2, "")&&"", SEQUENCE(LEN(A2) - LEN(SUBSTITUTE(A2, "", "")) + 1, 1)))))
我想要的是www, www_2, www_2_w4, www_2_w4_14。
英文:
I have cell value www_2_w4_14 for that I want to get all the combination like www, www_2, www_2_w4, www_2_w4_14. I have tried combinations of SPLIT, ARRAYFORMULA, JOIN, SUBSTITUTE functions but it doesn't seems to be working for me.
I tried below formulas but no success so far.
1> =LEFT(A2,FIND("@",SUBSTITUTE(A2,"_","@",LEN(A2)-LEN(SUBSTITUTE(A2,"_","")))))
2> =ArrayFormula(JOIN(",",ArrayFormula(LEFT(SPLIT(A2,"_")&"",SEQUENCE(LEN(A2)-LEN(SUBSTITUTE(A2,"_",""))+1,1)))))
What I want is www, www_2, www_2_w4, www_2_w4_14
答案1
得分: 2
You need SCAN() function.
=SCAN("", TOCOL(SPLIT(SUBSTITUTE(A1, "", "|"), "|")), LAMBDA(a, x, a & x))
英文:
You need SCAN() function.
=SCAN("",TOCOL(SPLIT(SUBSTITUTE(A1,"_","|_"),"|")),LAMBDA(a,x,a&x))
答案2
得分: 1
=LET(ζ,A1&"_",TEXTBEFORE(ζ,"_",SEQUENCE(LEN(ζ)-LEN(SUBSTITUTE(ζ,"_","")))))
英文:
This works in Excel, hopefully also in Sheets (can't test unfortunately):
=LET(ζ,A1&"_",TEXTBEFORE(ζ,"_",SEQUENCE(LEN(ζ)-LEN(SUBSTITUTE(ζ,"_","")))))
答案3
得分: 1
只是注意，楼主的原始公式（1>）基本上是正确的，并且可以按照以下方式进行操作：
=ArrayFormula(let(s,A2&"_",LEFT(S,FIND("@",SUBSTITUTE(s,"_","@",sequence(1,LEN(s)-LEN(SUBSTITUTE(S,"_","")))))-1)))
与 @Jos Woolley 的答案类似的逻辑 - 无需 lambda。
英文:
Just to note that OP's original formula (1>) is basically OK and can be made to work as follows:
=ArrayFormula(let(s,A2&"_",LEFT(S,FIND("@",SUBSTITUTE(s,"_","@",sequence(1,LEN(s)-LEN(SUBSTITUTE(S,"_","")))))-1)))
Similar logic to @Jos Woolley's answer - no lambda required.
答案4
得分: 0
这是一种方法：
=INDEX(REGEXREPLACE(REGEXEXTRACT(A1&"","(?:.*?){"&SEQUENCE(COUNTA(SPLIT(A1,"")))&"}"),"$",))
或
=SCAN(,SPLIT(A1,""),LAMBDA(a,c,REGEXREPLACE(a&""&c,"^_",))
英文:
Here's one way:
=INDEX(REGEXREPLACE(REGEXEXTRACT(A1&"_","(?:.*?_){"&SEQUENCE(COUNTA(SPLIT(A1,"_")))&"}"),"_$",))
Or
=SCAN(,SPLIT(A1,"_"),LAMBDA(a,c,REGEXREPLACE(a&"_"&c,"^_",)))
答案5
得分: 0
有点晚了，但这里有另一种方法：
=let(Σ,split(A1,"_"),
makearray(1,counta(Σ),lambda(r,c,join("_",array_constrain(Σ,1,c)))))
英文:
Bit late to the party but here's one more approach:
=let(Σ,split(A1,"_"),
makearray(1,counta(Σ),lambda(r,c,join("_",array_constrain(Σ,1,c)))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论