15问答网 > Excel中数字转换成汉字金额整数要后面要出现元整，带小数的不要元整怎么弄啊？

Excel中数字转换成汉字金额整数要后面要出现元整，带小数的不要元整怎么弄啊？

2024-12-03 18:43:30

推荐回答（5个）

回答1：

1、在A1输入数据
2、在B1粘贴以下字符：=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A1)),"[>0][dbnum2];[<0]负[dbnum2];;")&TEXT(RIGHT(FIXED(A1),2),"元[dbnum2]0角0分;;"&IF(ABS(A1)>1%,"元整",)),"零角",IF(ABS(A1)<1,,"零")),"零分","整")

回答2：

给您一个样本，单元格自己改一下。
=IF((INT(A11*10)-INT(A11)*10)=0,TEXT(INT(A11),"[DBNum2]G/通用格式")&"元"&IF((INT(A11*100)-INT((A11)*10)*10)=0,"整","零"&TEXT(INT(A11*100)-INT(A11*10)*10,"[DBNum2]G/通用格式")&"分"),TEXT(INT(A11),"[DBNum2]G/通用格式")&"元"&IF((INT(A11*100)-INT((A11)*10)*10)=0,TEXT((INT(A11*10)-INT(A11)*10),"[DBNum2]G/通用格式")&"角整",TEXT((INT(A11*10)-INT(A11)*10),"[DBNum2]G/通用格式")&"角"&TEXT(INT(A11*100)-INT(A11*10)*10,"[DBNum2]G/通用格式")&"分"))

回答3：

金额在A1中,任意单元格输入公式
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(A1<0,"负",)&TEXT(INT(ABS(A1)),"[dbnum2];; ")&TEXT(MOD(ABS(A1)*100,100),"[>9][dbnum2]元0角0分;[=0]元整;[dbnum2]元零0分"),"零分","整")," 圆零",)," 元",)

试试

回答4：

假设数字在A16:
=SUBSTITUTE(SUBSTITUTE(IF(A16>-0.5%,"人民币","负")&TEXT(INT(FIXED(ABS(A16))),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A16),2),"[dbnum2]0角0分;;"&IF(ABS(A16)>1%,"整",)),"零角",IF(ABS(A16)<1,,"零")),"零分","整")

回答5：

是大写金额吗