字符串取数运算讨论贴五

字符串取数运算讨论贴五
按要求用EXCEL求出结果。
数值 要求 结果 次数
13265412 顺序排列 11223456
13265412 不重复值 132654
13265412 不重复值顺序排列 123456
13265412 倒置 21456231
13265412 补漏 0789
13265412 重复次数数值如1次为3456，2次为12 12 2

1] 顺序排列

{=TEXT(SUM(LARGE(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1))))*10^(ROW(INDIRECT("1:"&LEN(A1)))-1)),REPT("0",LEN(A1)))}

2] 不重复值

{=IF(LEFT(A1)="0",0,"")&SUBSTITUTE(SUM(ISNUMBER(FIND(ROW($1:$9),A1))*MID(A1&(9-LEN(A1)),ROW($1:$9),1)*10^(9-ROW($1:$9))),0,"")}

3] 不重复值顺序排列

{=IF(COUNT(FIND(0,A1)),0,"")&SUBSTITUTE(SUM(ISNUMBER(FIND(ROW($1:$9),A1))*(ROW($1:$9))*10^(9-ROW($1:$9))),0,"")}

4] 倒置

=SUMPRODUCT(MID(A1,ROW(INDIRECT(1&":"&LEN(A1))),1)*(10^(ROW(INDIRECT(1&":"&LEN(A1)))-1)))

5] 补漏

{=IF(COUNT(FIND(0,A1)),0,"")&SUBSTITUTE(SUM(ISERR(FIND(ROW($1:$9),A1))*ROW($1:$9)*10^(9-ROW($1:$9))),0,"")}

6] 重复次数数值如1次

{=SUBSTITUTE(SUM(IF(LEN(A1)-LEN(SUBSTITUTE(A1,ROW($1:$9),))=1,ROW($1:$9)*10^(9-ROW($1:$9)))),0,)&IF(LEN(A1)-LEN(SUBSTITUTE(A2,0,))=1,0,"")}

7] 重复次数数值如2次

{=SUBSTITUTE(SUM(IF(LEN(A1)-LEN(SUBSTITUTE(A1,ROW($1:$9),))=2,ROW($1:$9)*10^(9-ROW($1:$9)))),0,)&IF(LEN(A1)-LEN(SUBSTITUTE(A1,0,))=2,0,"")}