（使用函数）拆分单元格内数据，拆分多行（上传附件）

wade_tan · 发表于 2023-3-25 21:43

拆分单元格内数据，拆分多行，如附件

ssg365 · 发表于 2023-3-25 22:08

=TEXTSPLIT(B2,{":"," "},CHAR(10))

ssg365 · 发表于 2023-3-26 06:55

本帖最后由 ssg365 于 2023-3-26 07:18 编辑

老版本也可以使用
=INDEX(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM($B$2),":"," "),CHAR(10)," ")," ","</b><b>")&"</b></a>","//b"),ROW(A1)*3+COLUMN(A1)-3)

或者将filterxml用mid函数来代替。

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM($B$2),":"," "),CHAR(10)," ")," ",REPT(" ",99)),(ROW(A1)-1)*297+COLUMN(A1)*99-98,99))

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