excel把一行拆成5列

假设原数据在A和B列，如图：

F2公式：

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(PHONETIC(B$2:B$100),",",""),"c",REPT(" ",LEN(SUBSTITUTE(PHONETIC(B$2:B$100),",","")))&"c"),LEN(SUBSTITUTE(PHONETIC(B$2:B$100),",",""))*ROW(A1)+1,LEN(SUBSTITUTE(PHONETIC(B$2:B$100),",",""))))

下拉F2填充柄，复制公式；

E2公式：

=INDEX(A$1:A$100,MAX(ISNUMBER(FIND(F2&",",B$1:B$100&","))*ROW($1:$100)))&""

数组公式，按组合键CTRL+SHIFT+回车完成公式，下拉E2填充柄，复制公式。