楼主
wshcw |
适用于十万元内大写逆转换为数字的公式,结题如下:
x.f.zhao
- =SUM(TEXT(FIND(MID(0&A3,FIND(MID("万仟佰拾元角分",ROW($1:8),1),A3&"万仟佰拾元角分"),1),"0整分万仟佰拾零壹贰叁肆伍陆柒捌玖")-8,"0;!0")*10^(5-ROW($1:8)))
wshcw(EXCEL2007版公式共98字)
- =SUM(IFERROR(FIND(MID(A3,FIND(MID("分角元拾佰仟万",ROW($1:8),1),A3)-1,1),"壹贰叁肆伍陆柒捌玖"),)*10^(ROW($1:8)-3))
wshcw
- =SUM(IF(ISERROR(FIND(MID(A3,FIND(MID("分角元拾佰仟万",ROW($1:8),1),A3)-1,1),"壹贰叁肆伍陆柒捌玖")),,FIND(MID(A3,FIND(MID("分角元拾佰仟万",ROW($1:8),1),A3)-1,1),"壹贰叁肆伍陆柒捌玖"))*10^(ROW($1:8)-3))
wshcw
- =SUM(TEXT(FIND(LEFT(MID(A3,FIND(MID("分角元拾佰仟万",ROW($1:$7),1),A3&"0分角元拾佰仟万")-1,1)&0),"壹贰叁肆伍陆柒捌玖0分角元拾佰仟万"),"[>9]\0")*10^(ROW($1:$7)-3))
chrisfang
- =ROUND(SUM(-REPLACE(MMULT(-({"1E-",""}&FIND(MID(SUBSTITUTE(A3,"零",),ROW($1:6)*2-{0,1},1),"00整分角元拾佰仟万壹贰叁肆伍陆柒捌玖")),{1;1})+10,3,1,))%%%,2)
chrisfang
- =RMB(SUM(-REPLACE(MMULT(-({"1E-",""}&FIND(MID(SUBSTITUTE(A3,"零",),ROW($1:6)*2-{0,1},1),"00整分角元拾佰仟万壹贰叁肆伍陆柒捌玖")),{1;1})+10,3,1,))%%%)
wshcw
- =SUM(TEXT(FIND(MID(A3,FIND(MID("分角元拾佰仟万",ROW($1:$7),1),A3&"0分角元拾佰仟万")-1,1),"0万仟佰拾壹贰叁肆伍陆柒捌玖")-5,"0;!0")*10^(ROW($1:$7)-3))
定义名称A:
- =IF(COUNT(FIND({"亿","万"},$A2)),IF(AND(ISERR(FIND("万",$A2)),COUNT(FIND({"亿","万"},$A2))=1),MID($A2,FIND("亿",$A2)+1,15),MID($A2,FIND("万",$A2)+1,15)),$A2)
公式:
- =IF(ISERR(FIND("万",A2)),,SUM((FIND(MID(A2,FIND({"万","拾","佰","仟","亿"},LEFT(A2,FIND("万",A2))&"零万零拾零佰零仟零亿")-1,1),"零壹贰叁肆伍陆柒捌玖")-1)*10^{4,5,6,7,8}))+IF(ISERR(FIND("亿",A2)),,FIND(LEFT(A2,FIND("亿",A2)-1),"壹贰叁肆伍陆柒捌玖")*10^8)+SUM(TEXT(FIND(MID(0&A,FIND(MID("分角元拾佰仟万",ROW($1:$7),1),0&A&"0分角元拾佰仟万")-1,1),"0整分万仟佰拾零壹贰叁肆伍陆柒捌玖")-8,"0;!0")*10^(ROW($1:$7)-3))
|