文心雕龙凤 发表于 2021-11-3 23:13:00

单元格的值转成数组

把单元格A1="220、316、318、320、406、408、402"的值变成数组
{220,316,318,320,406,408,402}

郑霖生 发表于 2021-11-4 12:48:46

好办法不会，凑个热闹：都是3位
=--TRANSPOSE(MID(A1,ROW(INDIRECT("1:"&(LEN(A1)+1)/4))*4-3,3))

位数不确定
=--TRANSPOSE(MID(SUBSTITUTE(A1,"、",REPT(" ",99)),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,"、",""))+1))*99-98,99))

吃俺老孙一棒 发表于 2021-11-7 04:14:31

大概这样子=transpose(trim(mid(substitute(a1,"、",rept(" ",99)),row(indirect("1:"&count(0/(mid(a1,row(1:99),1)="、")))*99-98,99))

刘世伟 发表于 2021-11-7 08:31:36

=--MID(A1,COLUMN(INDIRECT("C1:C"&(LEN(A1)+1)/4,))*4-3,3)

她叫凡凡 发表于 2021-12-7 17:09:55

=TRANSPOSE(FILTERXML("<a><b>"&SUBSTITUTE(A1,"、","</b><b>")&"</b></a>","//b"))

臧筱涛 发表于 2022-1-8 08:55:57

=EVALUATE("{"&SUBSTITUTE($A$1,"、",";")&"}")
WPS偷个鸡。

用户难忘 发表于 2022-1-9 14:51:34

C2=TRIM(MID(SUBSTITUTE(B2,"、",REPT(" ",99)),COLUMN(INDIRECT("C1:C"&LEN(B2)-LEN(SUBSTITUTE(B2,"、",))+1,))*99-98,99))*1

姜后仁者名 发表于 2022-1-10 11:07:31

让微软搞个split函数吧
页: [1]
查看完整版本: 单元格的值转成数组