【练习小题】压缩连续数

静听溪流

具体要求如下图，想看看这个题目可不可以不用开火车
我的公式307字符

象山海鲜

1. =SUBSTITUTE(MID(CONCAT(FILTERXML("<a><b a=','><c>-"&SUBSTITUTE(A2,",","</c></b><b a=','><c>-")&"</c></b></a>","//c[not(.+1=//c and .-1=//c)]|//c[not(.+1=//c)]/../@a")),3,99),",-",",")

复制代码

满坛皆为吾师

火车来了，前方小车危险
=SUBSTITUTE(SUBSTITUTE(TEXTJOIN({"-";","},,--RIGHT(0&MID(TRIM(MID(SUBSTITUTE(TRIM(CONCAT(TEXT(IFERROR(FIND(","&ROW($1:$999)&",",","&A2&",")^0*ROW($1:$999),),"000;; ")))," ",REPT(" ",99)),ROW($1:$23)*99-98,99)),{1,4},{3,99}),{4,3})),"-0",),",0",)

cutecpu

=SUBSTITUTE(TEXTJOIN("-",,FILTERXML("<a><c a='-'>"&SUBSTITUTE(A2,",","</c><c a='-'>")&"</c></a>","//c[not(.+1=//c or .=//c[last()])]/@a|//c[not(.+1=//c and .-1=//c)]")),"---",",")

祝順心，南無阿彌陀佛！

静听溪流

满坛皆为吾师 发表于 2020-7-5 21:21
火车来了，前方小车危险
=SUBSTITUTE(SUBSTITUTE(TEXTJOIN({"-";","},,--RIGHT(0&MID(TRIM(MID(SUBSTITUTE ...

这思路太赞啦
有点小瑕疵   当出现自然数大于999时结果错误

静听溪流

我的小火车
=SUBSTITUTE(CONCAT(FILTERXML("<a><b>"&SUBSTITUTE(A2,",","</b><b>")&"</b></a>","a/b[not(//b=.-1 and //b=.+1)]")&IF(IFERROR(FIND(","&FILTERXML("<a><b>"&SUBSTITUTE(A2,",","</b><b>")&"</b></a>","a/b[not(//b=.-1 and //b=.+1)]")+1&",",A2),),"-",","))&0,",0","")

FindingB

静听溪流 发表于 2020-7-5 22:31
我的小火车
=SUBSTITUTE(CONCAT(FILTERXML(""&SUBSTITUTE(A2,",","")&"","a/b[not(//b=.-1 and //b= ...

7,8,9,11,12、100,101或1000,1001 结果有误。

静听溪流

FindingB 发表于 2020-7-6 08:56
7,8,9,11,12、100,101或1000,1001 结果有误。

嗯  查找区域那边有点问题，已更改
=SUBSTITUTE(CONCAT(FILTERXML("<a><b>"&SUBSTITUTE(A2,",","</b><b>")&"</b></a>","a/b[not(//b=.-1 and //b=.+1)]")&IF(IFERROR(FIND(","&FILTERXML("<a><b>"&SUBSTITUTE(A2,",","</b><b>")&"</b></a>","a/b[not(//b=.-1 and //b=.+1)]")+1&",",A2&","),),"-",","))&0,",0","")

17601211544

你们都是大神无敌，但是我感觉工作中用处不会有。

17601211544

满坛皆为吾师 发表于 2020-7-5 21:21
火车来了，前方小车危险
=SUBSTITUTE(SUBSTITUTE(TEXTJOIN({"-";","},,--RIGHT(0&MID(TRIM(MID(SUBSTITUTE ...

太牛逼了，大神们，但是用到的机会基本没有吧