excel小写数值如何转换为大写

excel小写数值如何转换为大写

篇一：Excel表中小写金额直接转换成大写金额的公式

Excel表中小写金额直接转换成大写金额的公式：一、

=IF(OR(A1零",IF(VALUE(MID(A1,FIND(".",A1)+1,1))=0,"

",IF(ISERROR(FIND(".",A1)),"",NUMBERSTRING(MID(A1,FIND(".",A1)+1,1) ,2)&"角")))&IF(ISERROR(FIND(".",A1)),"",IF(LEN(ROUND(A1,2))=(FIND(".",A1)+2), NUMBERSTRING(MID(ROUND(A1,2),FIND(".",A1)+2,1),2)&"分","")))&IF(ISERROR(FIND(".",A1)),"","")

二、

假定你要在B1输入阿拉佰数字,C1转换成中文大写金额(含元角分),请在C1单元格输入如下公式:

=SUBSTITUTE(SUBSTITUTE(IF(-RMB(B1),IF(B1>0,,"")&TEXT(INT(ABS(B1)+ 0.5%),"[dbnum2]G/通用格负式元;;")&TEXT(RIGHT(RMB(B1,2),2),"[dbnum2]0角0分;;整"),),"零角",IF(B1三、

用公式就可以，我不懂做会计的如何用大写表示如128.40这样的角

不为0但分为0的值，是“壹佰贰拾捌元肆角整”还是“壹佰贰

拾捌元肆角”？

如果是“壹佰贰拾捌元肆角”，请用这个公式：

=TEXT(INT(B2),"[DBNUM2]")&"元"&IF(INT(B2)=B2,"整",(IF(INT(B2*10)-INT(B2)*10>0,TEXT(INT(B2*10)-INT(B2)*10,"[DBNUM2] ")&"角","零")&IF(B2*100-INT(B2*10)*10>0,TEXT(B2*100-INT(B2*10)*10,"[dbnum2] ")&"分","")))

如果是“壹佰贰拾捌元肆角整”，请用这个公式：

=TEXT(INT(B2),"[DBNUM2]")&"元"&(IF(INT(B2*10)-INT(B2)*10>0,TEXT(INT(B2*10)-INT(B2)*10,"[DBNUM2 ]")&"角",IF(B2*100-INT(B2*10)*10>0,"零",""))&IF(B2*100-INT(B2*10)*10>0,TEXT(B2*100-INT(B2*10)*10,"[dbnu m2]")&"分","整"))

说明：

1、将以上公式中的B2换成你要换算的值或对应的单元格。

2、公式中我只考虑了两位小数，后面如果还有小数，将被忽略。

excel2019

=IF(C10"),TEXT(INT(C10*10)-INT(C10)*10,"[dbnum2]")&"

")&IF((INT(C10*100)-INT(C10*10)*10)=0,"

",TEXT((INT(C10*100)-INT(C10*10)*10),"[dbnum2]")&"分") 零角整

篇二：EXCEL函数数字小写换大写