B2=SUM(RIGHT(SMALL(IFERROR(MID(C2,SMALL(IF(ISERR(-MID(C2,ROW($1:$98),1))*ISNUMBER(-MID(C2&"A",ROW($2:$99),1))*(MID(C2,ROW($1:$98),1)<>"."),ROW($2:$99)),ROW($1:$9)),COLUMN(A:I))*1,)+ROW($1:$9)*10^9,ROW($1:$9)*9),9)*1)
另解=SUMPRODUCT(TEXT(LEFT(TEXT(MID(C2&"a",COLUMN(A:Z),ROW($1:$26)),),ROW($1:$26)-1),"0%;-0%;0;!0")*ISERR(-MID(C2,COLUMN(A:Z)-1,2)))
版本新=SUM(REGEXP(C2,"\d+\.?\d+")*1)
留言
張貼留言