壹、自定義VBA函數：
Function RMBdx(Optional Mynum As Variant)
'原創：生哥
'來源：www.vip968.com?七彩陽光
'功能：根據數值返回人民幣的大寫金額。
If IsNumeric(Mynum) = False Then 'IsNumeric() 判斷是否為數字
Mynum = 0
End If
Mynum = Round(Mynum, 2)? '將數字保留2位小數
If Sgn(Mynum) = 0 Then '判斷數字是否為負數，=1為正，=0為零，=-1為負
RMBdx = "" '數字為0，則不顯示，如需顯示其它信息，請自行修改，如改為?RMBdx = "零圓"
Else
RMBdx = IIf(Sgn(Mynum) = -1, "負", "") & Application.Text(Int(Abs(Mynum)), "[=]g;[dbnum2]") & "圓"?'若為負數，則在前加“負”字
If Abs(Mynum) - Int(Abs(Mynum)) > 0 Then '判斷數字是否為帶小數點
?RMBdx = RMBdx & Application.Text(Right(Format(Abs(Mynum) - Int(Abs(Mynum)), "0.00"), 2), "[=]g;[dbnum2]0角0分")? '轉換小數點後數字
?RMBdx = Replace(Replace(RMBdx, "零分", ""), "零角", "零") ? '如出現“零角”則替換為“零”,如出現“零分”則清除，如需顯示“角整”則使用下邊壹行。
?'RMBdx = Replace(Replace(RMBdx, "零分", "整"), "零角", "零") ? '如出現“零角”則替換為“零”,如出現“零分”則清除，如需顯示“角整”則使用本行。
Else
?RMBdx = RMBdx & "整"
End If
End If
End Function
二、直接使用公式的最簡單方法：
1、顯示“角整”
如“10.20元”顯示為“壹拾圓貳角整”
=IF(ROUND(A1,2)=0,"",IF(A1<0,"負","")&IF(ABS(A1)>=1,TEXT(INT(ROUND(ABS(A1),2)),"[dbnum2]")&"圓","")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A1,2),2),"[dbnum2]0角0分;;整"),"零角",IF(A1^2<1,,"零")),"零分","整"))
2、不顯示“角整”
如“10.20元”顯示為“壹拾圓貳角”
=IF(ROUND(A1,2)=0,"",IF(A1<0,"負","")&IF(ABS(A1)>=1,TEXT(INT(ROUND(ABS(A1),2)),"[dbnum2]")&"圓","")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(A1,2),2),"[dbnum2]0角0分;;整"),"零角",IF(A1^2<1,,"零")),"零分",""))
三、用上述方法驗證如圖：c2公式：=rmbdx(B2)
d2公式：=IF(ROUND(B2,2)=0,"",IF(B2<0,"負","")&IF(ABS(B2)>=1,TEXT(INT(ROUND(ABS(B2),2)),"[dbnum2]")&"圓","")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(B2,2),2),"[dbnum2]0角0分;;整"),"零角",IF(B2^2<1,,"零")),"零分","整"))
e2公式：=IF(ROUND(B2,2)=0,"",IF(B2<0,"負","")&IF(ABS(B2)>=1,TEXT(INT(ROUND(ABS(B2),2)),"[dbnum2]")&"圓","")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(RMB(B2,2),2),"[dbnum2]0角0分;;整"),"零角",IF(B2^2<1,,"零")),"零分",""))
我覺得這個別人已經寫的很好了，這是我的學習筆記。看到這個問題就貼出來。
第三部分確實是我剛寫的公式。
不用的話就刪吧。