字符串取数运算讨论贴五
字符串取数运算讨论贴五按要求用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,"")}
页:
[1]