一行分多行

runningfish668

各位大佬，求助：
源数据：一个单元格有很多库区
诉求：把库区摘出来，并且每个库区分行
求指点，谢谢

祝洪忠-

1. =HSTACK(TOCOL(IF(BYROW(B3:B4,LAMBDA(x,COUNTA(TEXTSPLIT(x,,","))))>=COLUMN(A:Z),A3:A4,s),3),TEXTSPLIT(B3&B4,,{"""[","""",",","]""",""""},1))

复制代码

edwin11891

1. =REDUCE(G2:H2,A3:A4,LAMBDA(x,y,VSTACK(x,CHOOSE({1,2},y,TEXTSPLIT(SUBSTITUTES(OFFSET(y,,1),{"[","","]",""""}),,",")))))

复制代码

ww87725244

=REDUCE(G2:H2,A3:A4,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,TOCOL(REGEXEXTRACT(OFFSET(y,,1),"\d...",1))),y))))

无关岁月

1. =REDUCE(G2:H2,A3:A4,LAMBDA(_a,_b,VSTACK(_a,IF({1,0},_b,TOCOL(REGEXEXTRACT(OFFSET(_b,,1),"\w+",1))))))

复制代码

ykcbf1100

VBA参与一下，正则分行。

ykcbf1100

参与一下。。。

静听溪流

1. =TRANSPOSE(SCAN(,DROP(REDUCE(0,A2:A4,LAMBDA(x,y,HSTACK(x,VSTACK(y,TEXTSPLIT(REGEXREPLACE(OFFSET(y,,1),"([\""\[\]])","",0),","))))),,1),LAMBDA(x,y,IFNA(y,x))))

复制代码

沈默00

参与一下

=REDUCE(A2:B2,A3:A4,LAMBDA(x,y,VSTACK(x,HSTACK(IF(TOCOL(REGEXP(OFFSET(y,,1),"\w+"),3)<>"",y,""),TOCOL(REGEXP(OFFSET(y,,1),"\w+"),3)))))

hcm19522

I3:I20{=INDEX(A:A,SMALL(IF(LEN(B$3:B$4)-LEN(SUBSTITUTE(B$3:B$4,",",))+1>=COLUMN(A:Z),{3;4},9),ROW(A1)))&""

J3:J20=IF(I3="","",MID(TRIM(MID(SUBSTITUTE(SUBSTITUTE(VLOOKUP(I3,A:B,2,),"""",),",",REPT(" ",99)),COUNTIF(I$3:I3,I3)*99-98,99)),2+(COUNTIF(I$3:I3,I3)=1),4))