Question

在EXCEL表格里怎样自动生成金额大写

例如：213.13元，生成贰佰壹拾叁元壹角叁分
谢谢
我试过在单元格设置里只能是数字的大写，而不是金额，只会变成贰佰壹拾叁元。壹叁

Answer 1

=TEXT(INT(A1),"[dbnum2]")&IF(INT(A1)=A1,"元整","元")&IF(ISERROR(FIND(".",A1))=TRUE,"",IF(LEN(A1)-FIND(".",A1)=1,TEXT(RIGHT(A1,1),"[dbnum2]")&"角",IF(AND(LEN(A1)-FIND(".",A1)=2,MID(A1,LEN(A1)-1,1)<>"0"),TEXT(MID(A1,LEN(A1)-1,1),"[dbnum2]")&"角"&TEXT(RIGHT(A1,1),"[dbnum2]")&"分","零"&TEXT(RIGHT(A1,1),"[dbnum2]")&"分")))

Answer 2

选择单元格，右击鼠标，设置单元格格式，数字标签项的分类选择特殊，里面就有转换大写的。

Answer 3

加入你把数字放在单元格A1中
直接在任一单元格输入公式（复制黏贴）

=IF((A1-INT(A1))=0,TEXT(A1,"[DBNUM2]")&"元",IF(INT(A1*10)-A1*10=0,TEXT(INT(A1),"[DBNUM2]")&"元"&TEXT((INT(A1*10)-INT(A1)*10),"[DBNUM2]")&"角",TEXT(INT(A1),"[DBNUM2]")&"元"&IF(INT(A1*10)-INT(A1)*10=0,"零",TEXT(INT(A1*10)-INT(A1)*10,"[DBNUM2]")&"角")&TEXT(RIGHT(A1,1),"[DBNUM2]")&"分"))

回车就行了
这个公式看似复杂，绝对好用

Answer 4

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(INT(G5),"[dbnum2]G/通用格式元"&IF(MOD(G5,1),"","整")&";;;")&TEXT(--RIGHT(INT(G5*10)),"[dbnum2][>0]#角;[=0]")&TEXT(--RIGHT(INT(G5*100)),"[dbnum2][>0]#分;[=0]整"),"元0","元零"),0,),"整整","整")