Excel精英培训网

 找回密码
 注册

QQ登录

只需一步,快速开始

工作中常用的Excel函数公式,全印在一张超大鼠标垫上
查看: 262|回复: 4

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

[复制链接]
发表于 2021-7-22 12:10 | 显示全部楼层 |阅读模式
用公式将包含在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;>=@")&"]"),""))
复制代码
Capture.JPG

test.rar

10.23 KB, 下载次数: 14

excel精英培训的微信平台,每天都会发送excel学习教程和资料。扫一扫明天就可以收到新教程
发表于 2021-7-22 16:31 | 显示全部楼层
本帖最后由 hcm19522 于 2021-7-23 09:50 编辑

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,"")
8552.png
回复

使用道具 举报

发表于 2021-7-23 07:56 | 显示全部楼层
回复

使用道具 举报

发表于 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;>=@")&"]"),""))
复制代码
回复

使用道具 举报

发表于 2021-9-6 13:52 | 显示全部楼层
{=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")))))}
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2021-9-16 19:47 , Processed in 0.488383 second(s), 10 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表