怎么把单元格的内容拆分转置

四宫

请教各位大神，如何用函数批量把B列单元格的姓名拆分转置成E、F列这样的效果。

solextrade

=LOOKUP(ROW(A1)-1,MMULT(N(ROW($1:$6)>COLUMN(A:F)),LEN(B$2:B$7)-LEN(SUBSTITUTE(B$2:B$7,"、",))+1),A$2:A$7)&""

=TRIM(MID(SUBSTITUTE(VLOOKUP(E2,A:B,2,),"、",REPT(" ",19)),COUNTIF(E$2:E2,E2)*19-18,19))

消灭零公里

加载到PQ，拆分到行

hugeinfo

=INDEX(TEXTSPLIT(TEXTJOIN("",,REPT($A$2:$A$6&"#",LEN(B$2:B$6)-LEN(SUBSTITUTE(B$2:B$6,"、",))+1)),,"#"),ROW(A1))
=INDEX(TEXTSPLIT(TEXTJOIN("、",,$B$2:$B$6),,"、"),ROW(A1))

zpy2

https://club.excelhome.net/forum.php?mod=viewthread&tid=1663026&fromguid=hot&extra=&mobile&_dsign=be995f29

高个子

1. =INDEX(A:A,SMALL(IF(LEN($B$1:$B$6)-LEN(SUBSTITUTE($B$1:$B$6,"、",""))+1>=COLUMN($A:$Z),ROW($1:$6)),ROW(A1)))

复制代码

1. =TRIM(MID(SUBSTITUTE(INDEX($B:$B,MATCH(E1,A:A,0)),"、",REPT(" ",99)),COUNTIF($E$1:E1,E1)*99-98,99))

复制代码

三键

xlllw203

先做姓名

区域数组

1. F2：F13 =FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN("、",,B2:B6),"、","</b><b>")&"</b></a>","a/b")

复制代码

后做场次

1. E2 =INDEX(A:A,MATCH("*"&F2&"*",$B$2:$B$6,)+1)

复制代码

下拉

博爱风雨同舟

=REDUCE({"场次","姓名"},A2:A6,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(y,TEXTSPLIT(OFFSET(y,,1),,"、")),y))))
365版本

袁勇

sucx007

365函数，常规做法

1. =REDUCE(A1:B1,A2:A6,LAMBDA(x,y,VSTACK(x,IF({1,0},y,TEXTSPLIT(OFFSET(y,,1),,"、")))))

复制代码