Excel数字自动转换大写金额
作者:绿野霰踪 日期:2009-02-07
把A27换成你想要的单元格(这个是数据单元格)， 这个代码放在任意单元格内，就可以看到效果了
=IF(A27<0,REPLACE(IF(TRUNC(A27)=A27,TEXT(A27,"[DBNum2]")&"元整",IF(TRUNC(A27*10)=A27*10,TEXT(TRUNC(A27),"[DBNum2]")&"元"&TEXT(RIGHT(A27),"[DBNum2]")&"角整",TEXT(TRUNC(A27),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A27)),"零",TEXT(LEFT(RIGHT(A27,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A27),"[DBNum2]")&"分")),1,1,"负"),IF(A27=0," ",IF(TRUNC(A27)=A27,TEXT(A27,"[DBNum2]")&"元整",IF(TRUNC(A27*10)=A27*10,TEXT(TRUNC(A27),"[DBNum2]")&"元"&TEXT(RIGHT(A27),"[DBNum2]")&"角整",TEXT(TRUNC(A27),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A27)),"零",TEXT(LEFT(RIGHT(A27,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A27),"[DBNum2]")&"分"))))
=IF(A27<0,REPLACE(IF(TRUNC(A27)=A27,TEXT(A27,"[DBNum2]")&"元整",IF(TRUNC(A27*10)=A27*10,TEXT(TRUNC(A27),"[DBNum2]")&"元"&TEXT(RIGHT(A27),"[DBNum2]")&"角整",TEXT(TRUNC(A27),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A27)),"零",TEXT(LEFT(RIGHT(A27,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A27),"[DBNum2]")&"分")),1,1,"负"),IF(A27=0," ",IF(TRUNC(A27)=A27,TEXT(A27,"[DBNum2]")&"元整",IF(TRUNC(A27*10)=A27*10,TEXT(TRUNC(A27),"[DBNum2]")&"元"&TEXT(RIGHT(A27),"[DBNum2]")&"角整",TEXT(TRUNC(A27),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A27)),"零",TEXT(LEFT(RIGHT(A27,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A27),"[DBNum2]")&"分"))))
