求大神帮助怎么将规格型号编号展开

直飚oヤ零℃ · 发表于 2022-2-23 17:51

规格型号AL1、2怎么变成AL1 AL2

博爱风雨同舟 · 发表于 2022-2-23 18:41

C4=MID(B4,1,FIND("~",B4)-2)&TRANSPOSE(SEQUENCE(--MID(B4,FIND("~",B4)+1,1)))

博爱风雨同舟 · 发表于 2022-2-23 18:45

C4=LET(X,FIND("~",B4),MID(B4,1,X-2)&TRANSPOSE(SEQUENCE(--MID(B4,X+1,1)))&MID(B4,X+2,5))

博爱风雨同舟 · 发表于 2022-2-23 18:57

C2=LET(Y,FIND("、",B2),X,FIND("~",B2),IFERROR(MID(B2,1,X-2)&TRANSPOSE(SEQUENCE(--MID(B2,X+1,1)))&MID(B2,X+2,5),MID(B2,1,Y-2)&TRANSPOSE(SEQUENCE(--MID(B2,Y+1,1)))&MID(B2,Y+2,5)))
365版本。

jisijie630 · 发表于 2022-2-23 21:00

本帖最后由 jisijie630 于 2022-2-23 22:25 编辑

简化一下：
=IFERROR(LEFT($B2,LOOKUP(99,FIND({"~","、"},$B2))-2)&SMALL(ROW(INDIRECT("1:"&MAX((ISNUMBER(FIND({"~","、"}&ROW($1:$9),$B2))*ROW($1:$9))))),COLUMN(A1))&MID($B2,LOOKUP(99,FIND({"~","、"},$B2))+2,9),"")
数组三键下拉右拉。

博爱风雨同舟 · 发表于 2022-2-24 10:18

C2=LET(X,LOOKUP(99,FIND({"~","、"},B2)),MID(B2,1,X-2)&TRANSPOSE(SEQUENCE(--MID(B2,X+1,1)))&MID(B2,X+2,5))
365版本

直飚oヤ零℃ · 发表于 2024-10-22 15:03

jisijie630 发表于 2022-2-23 21:00
简化一下：
=IFERROR(LEFT($B2,LOOKUP(99,FIND({"~","、"},$B2))-2)&SMALL(ROW(INDIRECT("1:"&MAX((ISNUMB ...

太牛逼了，感谢

无关岁月 · 发表于 2024-10-22 15:37

=REGEXEXTRACT(B2,"[\w-]+(?=1[、~])")&SEQUENCE(,REGEXEXTRACT(B2,"(?<=[、~])\d+"))&REGEXEXTRACT(B2,"-\d+$|$")

复制代码

mykitami · 发表于 2024-10-22 15:58

=LET(t,SUBSTITUTE(B2,"-","x"),a,TEXTAFTER(t,{"、","~"}),aa,-LOOKUP(1,-LEFT(a,SEQUENCE(9))),q,TEXTBEFORE(t,{"、","~"}),qq,-LOOKUP(1,-RIGHT(q,SEQUENCE(9))),s,qq&"~"&aa,SUBSTITUTE(SUBSTITUTE(B2,"、","~"),s,TOROW(ROW(INDIRECT(qq&":"&aa)))))

dongyf · 发表于 2024-10-26 15:56

=TEXTBEFORE(B2,{"1、","1~"})&SEQUENCE(,--REGEXEXTRACT(B2,"(?<=[~、])\d"))&REGEXEXTRACT(B2,"-\d$|$")

		自动登录	找回密码
密码			免费注册

[求助] 求大神帮助怎么将规格型号编号展开

评分

评分

评分