第1个回答 2013-08-23
右键选该单元格,设置单元格格式,数字,特殊,中文大写(2)文字和数字组合比如数字在A1格为456,要在B1格得到肆佰伍拾陆元整,函数如下“=TEXT(A1,"[Dbnum2]")&"元整"”,其中[dbnum2] 是格式函数, 数字转中文大写,有一内建函数与 [dbnum2]有相同作用, 是numberstring 以上函数也可以写成“=numberstring(A1,2)&"元整"”(3)复杂数字转中文大写比如,要将A1格的123456.65转变为壹拾贰万叁仟肆佰伍拾陆圆陆角伍分,在B1格输入以下公式“=TEXT(TRUNC(A1),"[Dbnum2]")&"圆"&IF(ROUND((A1-TRUNC(A1))*100,1)<0,IF(LEN(ROUND((A1-TRUNC(A1))*100,1))=1,"零角"&TEXT(ROUND((A1-TRUNC(A1))*100,1),"[Dbnum2]")&"分",TEXT((ROUND((A1-TRUNC(A1))*100,1)-MOD(ROUND((A1-TRUNC(A1))*100,1),10))/10,"[Dbnum2]")&"角"&TEXT(MOD(ROUND((A1-TRUNC(A1))*100,1),10),"[Dbnum2]")&"分"),"零角零分 ")”另有网友编为:“=IF((INT(A1*10)-INT(A1)*10)=0,TEXT(INT(A1),"[DBNum2]")&"圆"&IF((INT(A1*100)-INT((A1)*10)*10)=0,"整","零"&TEXT(INT(A1*100)-INT(A1*10)*10,"[DBNum2]")&"分"),TEXT(INT(A1),"[DBNum2]")&"圆"&IF((INT(A1*100)-INT((A1)*10)*10)=0,TEXT((INT(A1*10)-INT(A1)*10),"[DBNum2]")&"角整",TEXT((INT(A1*10)-INT(A1)*10),"[DBNum2]")&"角"&TEXT(INT(A1*100)-INT(A1*10)*10,"[DBNum2]")&"分"))”也不错本回答被提问者采纳
第2个回答 2015-09-10
假设A4为小写数字,可在B4输入以下公式:
=SUBSTITUTE(SUBSTITUTE(IF(ROUND(A4,2),TEXT(A4,";负")&TEXT(INT(ABS(A4)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(TEXT(A4,".00"),2),"[dbnum2]0角0分;;整"),),"零角",IF(A4^2<1,,"零")),"零分","整")