如何将20610.03元转为中文大写，急!!!!!!

沐琦

excel中将206010.03元实现中文大写贰拾万零陆仟零壹拾元零叁分

wshcw

=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A1),IF(A1>0,,"负")&TEXT(INT(ABS(A1)+0.5%),"[DBNUM2]G/通用格式元;;")&TEXT(RIGHT(RMB(A1),2),"[DBNUM2]0角0分;;整"),),"零角",IF(A1^2<1,,"零")),"零分","整")

gufengaoyue

1. 
   
2. =TEXT(INT(A1),"[DBNum2]G/通用格式元")&TEXT(MID(A1*100,LEN(A1*100)-1,1),"[DBNum2]0角")&TEXT(RIGHT(A1*100),"[DBNum2]0分")

复制代码

[ 本帖最后由 gufengaoyue 于 2011-3-14 14:38 编辑 ]

沐琦

首先谢谢了，但您的公式无法实现贰拾万和元后面有零字，这是银行写支票所要求有

wshcw

没有负,而小数点最多两位时,公式简化为:
=IF(A1,SUBSTITUTE(TEXT(INT(A1),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(A1/1%,2),"[>9][DBNUM2]0角0分;"&IF(A1>1,0,)&"[>][DBNUM2]0分;整"),"零分",),"")

wshcw

￥大写合集

wshcw

够完美解决,只是公式加到251个字符了:  
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(-RMB(A1,2),TEXT(A1,";负")&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A1,2),2),"[dbnum2]0角0分;;整"),),"零角",IF(A1^2<1,,"零")),"万",IF(AND(MOD(ABS(A1%),1000)<100,MOD(ABS(A1%),1000)>=10),"万零","万")),"零分","整")  
如:1000103005.26  
转换为:  
壹拾亿零壹拾万零叁仟零伍元贰角陆分

沐琦

excel中将206010.03元实现中文大写贰拾万零陆仟零壹拾元零叁分

沐琦

在excel中200610.30元中，你给我的公式只能做得到 贰拾万陆仟零壹拾元叁角整
而我要的是贰拾万零陆仟零壹拾元零叁角整这样的效果

wshcw

那再套一下:
=SUBSTITUTE(SUBSTITUTE("原公式","拾元","拾元零"),"零零","零")