本帖最后由 wlc52011300 于 2013-4-9 20:17 编辑
占个位置
超长的
1,=LEFT(TEXT(SUM(MID(A2,SMALL(IF(NOT(ISNUMBER(FIND(MID(A2,ROW($1:$10)*2-1,1),B2))),ROW($1:$10)*2-1,99),ROW(INDIRECT("1:"&(10-COUNT((FIND(MID(A2,ROW($1:$10)*2-1,1),B2))))))),1)*10^((10-COUNT((FIND(MID(A2,ROW($1:$10)*2-1,1),B2))))-ROW(INDIRECT("1:"&(10-COUNT((FIND(MID(A2,ROW($1:$10)*2-1,1),B2)))))))),REPT("0!,",(10-COUNT((FIND(MID(A2,ROW($1:$10)*2-1,1),B2)))))),(10-COUNT((FIND(MID(A2,ROW($1:$10)*2-1,1),B2))))*2-1)
2,=LEFT(RIGHT(TEXT(LEFT(SUM(--TEXT(MID(A2,SMALL(IF(NOT(ISNUMBER(FIND(MID(A2,ROW($1:$10)*2-1,1),B2))),ROW($1:$10)*2-1,99),ROW($1:$10)),1),"0;;0;!0")*10^(10-ROW($1:$10))),10-COUNT((FIND(MID(A2,ROW($1:$10)*2-1,1),B2)))),REPT("0!,",10)),(10-COUNT((FIND(MID(A2,ROW($1:$10)*2-1,1),B2))))*2),(10-COUNT((FIND(MID(A2,ROW($1:$10)*2-1,1),B2))))*2-1)
3,=SUBSTITUTE(TEXT(SUM(TEXT(MID(A2,SMALL(IF(ISERR(FIND(MID(A2,ROW($1:$10)*2-1,1),B2)),ROW($1:$10)*2-1,99),ROW($1:$10)),1),"0;;0;!0")*10^(10-ROW($1:$10))),REPT("0!,",10)),","&REPT("0,",COUNT(FIND(MID(A2,ROW($1:$10)*2-1,1),B2))),)
=TEXT(SUM(MID(A2&REPT(0,99),TEXT(SMALL(ISERR(FIND(MID(A2,ROW($1:$10)*2-1,1),B2))*(ROW($1:$10)*2-1),ROW($1:$10)),"0;0;99"),1)*10^(10-ROW($1:$10))),REPT("0!,",9-COUNT(FIND(MID(A2,ROW($1:$10)*2-1,1),B2)))&0)
数组
不包含“=”168字符
=TEXT(SUM(MID(0&A2,SMALL(ISERR(FIND(MID(A2,ROW($1:$20),1),B2))*ROW($1:$20),ROW($1:$20))+1,1)*10^(20-ROW($1:$20))),REPT("0!,",19-COUNT(FIND(MID(A2,ROW($1:$20),1),B2)))&0)
数组
|