[已解决]将包含在A列的数字全部列出并用逗号隔开

cabcyvr

用公式将包含在A列的数字全部列出并用逗号隔开, 可以使用Text join， 谢谢！

最佳答案

月排行榜 / 总排行榜

一剑惊心

2021-7-23 08:19

1. =TEXTJOIN(",",,IFERROR(IF((COLUMN(A:CU)>=--LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",20)),ROW($1:$9)*21-20,20)),"-"," "),2))*(COLUMN(A:CU)<=--RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",20)),ROW($1:$9)*21-20,20)),"-"," "),2)),COLUMN(A:CU),""),""))

复制代码

1. =TEXTJOIN(",",,IFERROR(FILTERXML("<a>"&ROW($1:$99)&"</a>","a[."&TEXT(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",99)),COLUMN(A:G)*99-98,100)),"-","][.<="),"=0;>=@")&"]"),""))

复制代码

跳转到最佳答案楼层

hcm19522

2007版   
C2 右拉 下拉 {=IFERROR(IF(COLUMN(A1)=1,"",",")&SMALL(IF((COLUMN($A:$CU)-(0&TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE($A2,",",REPT(" ",29)),{1;2;3;4;5}*29-28,29),"-",REPT(" ",29)),29)))>=0)*(COLUMN($A:$CU)-(0&TRIM(RIGHT(SUBSTITUTE(MID(SUBSTITUTE($A2,",",REPT(" ",29)),{1;2;3;4;5}*29-28,29),"-",REPT(" ",29)),29)))<=0),COLUMN($A:$CU)),COLUMN(A1))&D2,"")

shenlong2006

学习一下

一剑惊心

1. =TEXTJOIN(",",,IFERROR(IF((COLUMN(A:CU)>=--LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",20)),ROW($1:$9)*21-20,20)),"-"," "),2))*(COLUMN(A:CU)<=--RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",20)),ROW($1:$9)*21-20,20)),"-"," "),2)),COLUMN(A:CU),""),""))

复制代码

1. =TEXTJOIN(",",,IFERROR(FILTERXML("<a>"&ROW($1:$99)&"</a>","a[."&TEXT(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",99)),COLUMN(A:G)*99-98,100)),"-","][.<="),"=0;>=@")&"]"),""))

复制代码

yunqingzi

{=TEXTJOIN(",",,ROW(INDIRECT(MIN(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A2,",","-"),"-","</b><b>")&"</b></a>","//b"))&":"&MAX(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A2,",","-"),"-","</b><b>")&"</b></a>","//b")))))}