提取第一个冒号后的数字=LOOKUP(E9+307,--LEFT(RIGHT(A1,LEN(A1)-2)))
提取第二个冒号后的数字=LOOKUP(E9+307,--RIGHT(A1,ROW($1:$1000)))
四个加一块即可。关键是有空格,需要用isnumber判断是数字还是错误。
E1=IF(ISNUMBER(LOOKUP(9E+307,--LEFT(RIGHT(A1,LEN(A1)-2),ROW($1:$20)))),LOOKUP(9E+307,--LEFT(RIGHT(A1,LEN(A1)-2),ROW($1:$20))),0)+IF(ISNUMBER(LOOKUP(9E+307,--RIGHT(A1,ROW($1:$1000)))),LOOKUP(9E+307,--RIGHT(A1,ROW($1:$1000))),0)+IF(ISNUMBER(LOOKUP(9E+307,--LEFT(RIGHT(B1,LEN(B1)-2),ROW($1:$20)))),LOOKUP(9E+307,--LEFT(RIGHT(B1,LEN(B1)-2),ROW($1:$20))),0)+IF(ISNUMBER(LOOKUP(9E+307,--RIGHT(B1,ROW($1:$1000)))),LOOKUP(9E+307,--RIGHT(B1,ROW($1:$1000))),0)+IF(ISNUMBER(LOOKUP(9E+307,--LEFT(RIGHT(C1,LEN(C1)-2),ROW($1:$20)))),LOOKUP(9E+307,--LEFT(RIGHT(C1,LEN(C1)-2),ROW($1:$20))),0)+IF(ISNUMBER(LOOKUP(9E+307,--RIGHT(C1,ROW($1:$1000)))),LOOKUP(9E+307,--RIGHT(C1,ROW($1:$1000))),0)+IF(ISNUMBER(LOOKUP(9E+307,--LEFT(RIGHT(D1,LEN(D1)-2),ROW($1:$20)))),LOOKUP(9E+307,--LEFT(RIGHT(D1,LEN(D1)-2),ROW($1:$20))),0)+IF(ISNUMBER(LOOKUP(9E+307,--RIGHT(D1,ROW($1:$1000)))),LOOKUP(9E+307,--RIGHT(D1,ROW($1:$1000))),0)