sxt66329的部落格

時常因工作需要，將數字想將它轉換成大寫金額，有時利用儲存格之格式設定，也是無法解決，我搜尋有位網友，彙整了35種函數方法，列舉三個方法讓各位了解，如有需要，請至http://pan.baidu.com/s/1eQmYC0q下載。

方法一

TEXT(INT(A5),"[DBNum2]")&"元"&TEXT(INT(A5*10)-INT(A5)*10,"[DBNum2]")&"角"&TEXT(INT(A5*100)-INT(A5*10)*10,"[DBNum2]")&"分"

結果如下圖

方法二

IF(A5<0,REPLACE(IF(TRUNC(A5)=A5,TEXT(A5,"[DBNum2]")&"元整",IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(A5),"[DBNum2]")&"元"&TEXT(RIGHT(A5),"[DBNum2]")&"角整",TEXT(TRUNC(A5),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A5)),"零",TEXT(LEFT(RIGHT(A5,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A5),"[DBNum2]")&"分")),1,1,"负"),IF(TRUNC(A5)=A5,TEXT(A5,"[DBNum2]")&"元整",IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(A5),"[DBNum2]")&"元"&TEXT(RIGHT(A5),"[DBNum2]")&"角整",TEXT(TRUNC(A5),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A5)),"零",TEXT(LEFT(RIGHT(A5,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A5),"[DBNum2]")&"分")))

結果如下圖

方法三

IF(A5<0,REPLACE(IF(TRUNC(A5)=A5,TEXT(A5,"[DBNum2]")&"元整",IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(A5),"[DBNum2]")&"元"&TEXT(RIGHT(A5),"[DBNum2]")&"角整",TEXT(TRUNC(A5),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A5)),"零",TEXT(LEFT(RIGHT(A5,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A5),"[DBNum2]")&"分")),1,1,"負"),IF(TRUNC(A5)=A5,TEXT(A5,"[DBNum2]")&"元整",IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(A5),"[DBNum2]")&"元"&TEXT(RIGHT(A5),"[DBNum2]")&"角整",TEXT(TRUNC(A5),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A5)),"零",TEXT(LEFT(RIGHT(A5,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A5),"[DBNum2]")&"分")))

結果如下圖