数字转换成大写数字后带有小数点比如125.26
显示的是：壹佰贰拾伍.贰陆
我要显示成：壹佰贰拾伍元贰角陆分
求高手解答，网上找的宏貌似在EXCEL里无法应用
第一个问题已解决谢谢了！！
还有个问题，我要在A1中输入“张三”，我在B2中输入格式“=A1”，当我在A1写“张三”时，在B2中能正确显示“张三”，但当我不需要在A1中输入“张三”时，在B2中显示的是“0”
这个我就很郁闷了，怎么A1中不输入文字时，B2不会显示0，让B2空白着
假设数字在A1
在B1输入公式
=IF(OR(A1="",A1=0),"",TEXT(INT(A1),"[dbnum2]G/通用格式圆;负[dbnum2]G/通用格式圆;;")&TEXT(--RIGHT(INT(A1*10)),"[dbnum2]#角;;;")&TEXT(--RIGHT(INT(A1*100)),"[dbnum2]#分;;整;"))
或者
=SUBSTITUTE(IF(-RMB(A1),IF(A1>0,,"负")&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A1),2),"[>9][dbnum2]0角0分;"&IF(A1^2<1,,0)&"[>][dbnum2]0分;整"),),"零分","整")
就会在B1显示成：壹佰贰拾伍元贰角陆分
怎么A1中不输入文字时，B2不会显示0，让B2空白着
菜单栏，工具－选项－视图，取消“零值”的勾
在B1输入公式
=IF(OR(A1="",A1=0),"",TEXT(INT(A1),"[dbnum2]G/通用格式圆;负[dbnum2]G/通用格式圆;;")&TEXT(--RIGHT(INT(A1*10)),"[dbnum2]#角;;;")&TEXT(--RIGHT(INT(A1*100)),"[dbnum2]#分;;整;"))
或者
=SUBSTITUTE(IF(-RMB(A1),IF(A1>0,,"负")&TEXT(INT(ABS(A1)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A1),2),"[>9][dbnum2]0角0分;"&IF(A1^2<1,,0)&"[>][dbnum2]0分;整"),),"零分","整")
就会在B1显示成：壹佰贰拾伍元贰角陆分
怎么A1中不输入文字时，B2不会显示0，让B2空白着
菜单栏，工具－选项－视图，取消“零值”的勾
温馨提示：答案为网友推荐，仅供参考
第1个回答 推荐于2016-11-17
EXCEL将数字转换成中文大写数字后如何显示角分元整：
用公式,假如A1是你的数据,在B1输入如下公式试试:
=TEXT(INT(A1),"[DBNum2]")&IF(ISERROR(FIND(".",A1)),"元整","元")&IF(ISERROR(FIND(".",A1)),"",IF(MID(A1,FIND(".",A1)+1,1)="0",IF(MID(A1,FIND(".",A1)+2,1)="","",TEXT(MID(A1,FIND(".",A1)+2,1),"[DBNum2]")&"分"),IF(MID(A1,FIND(".",A1)+2,1)="",TEXT(MID(A1,FIND(".",A1)+1,1),"[DBNum2]")&"角整",TEXT(MID(A1,FIND(".",A1)+1,1),"[DBNum2]")&"角"&TEXT(MID(A1,FIND(".",A1)+2,1),"[DBNum2]")&"分")))
用公式,假如A1是你的数据,在B1输入如下公式试试:
=TEXT(INT(A1),"[DBNum2]")&IF(ISERROR(FIND(".",A1)),"元整","元")&IF(ISERROR(FIND(".",A1)),"",IF(MID(A1,FIND(".",A1)+1,1)="0",IF(MID(A1,FIND(".",A1)+2,1)="","",TEXT(MID(A1,FIND(".",A1)+2,1),"[DBNum2]")&"分"),IF(MID(A1,FIND(".",A1)+2,1)="",TEXT(MID(A1,FIND(".",A1)+1,1),"[DBNum2]")&"角整",TEXT(MID(A1,FIND(".",A1)+1,1),"[DBNum2]")&"角"&TEXT(MID(A1,FIND(".",A1)+2,1),"[DBNum2]")&"分")))
第2个回答 2010-02-01
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(NUMBERSTRING(INT(A1),2)&"圆"&TEXT(MOD(A1,1)*100,"[dbnum2]0角0分"),"零角零分","整"),"零角","零"),"零分","")
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(NUMBERSTRING(INT(A1),2)&"圆"&TEXT(MOD(A1,1)*100,"[dbnum2]0角0分"),"零角零分","整"),"零角","零"),"零分","整")
b2 =if(a1="","",a1)
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(NUMBERSTRING(INT(A1),2)&"圆"&TEXT(MOD(A1,1)*100,"[dbnum2]0角0分"),"零角零分","整"),"零角","零"),"零分","整")
b2 =if(a1="","",a1)
