Excel?中小写数字转换为大写金额的方法

1。A6中是小写的数据，在B6中用公式转换大写，公式如下：
=IF(A6<0,"(负)"&IF(ABS(A6)<1,"",TEXT(INT(ABS(A6)),"[dbnum2]")&"元")&IF(MOD(INT(ABS(A6*10)),10)=0,IF(INT(ABS(A6))*MOD(INT(ABS(A6*100)),10)=0,"","零"),TEXT(MOD(INT(ABS(A6*10)),10),"[dbnum2]")&"角")&IF(MOD(INT(ABS(A6*100)),10)=0,"整",TEXT(MOD(INT(ABS(A6*100)),10),"[dbnum2]")&"分"),IF(A6=0,"零",IF(A6<1,"",TEXT(INT(A6),"[dbnum2]")&"元")&IF(MOD(INT(A6*10),10)=0,IF(INT(A6)*MOD(INT(A6*100),10)=0,"","零"),TEXT(MOD(INT(A6*10),10),"[dbnum2]")&"角")&IF(MOD(INT(A6*100),10)=0,"整",TEXT(MOD(INT(A6*100),10),"[dbnum2]")&"分")))
说明：A6中是小写的数据。公式中已考虑负数与零数的问题。但没有考虑四舍五入，所以如小数三位的话，请在数据原始区域先做好四舍五入处理。如一定要在公式中一并处理，公式如下：
=IF(ROUND(A3,2)<0,"(负)"&IF(ABS(ROUND(A3,2))<1,"",TEXT(INT(ABS(ROUND(A3,2))),"[dbnum2]")&"元")&IF(MOD(INT(ABS(ROUND(A3,2)*10)),10)=0,IF(INT(ABS(ROUND(A3,2)))*MOD(INT(ABS(ROUND(A3,2)*100)),10)=0,"","零"),TEXT(MOD(INT(ABS(ROUND(A3,2)*10)),10),"[dbnum2]")&"角")&IF(MOD(INT(ABS(ROUND(A3,2)*100)),10)=0,"整",TEXT(MOD(INT(ABS(ROUND(A3,2)*100)),10),"[dbnum2]")&"分"),IF(ROUND(A3,2)=0,"零",IF(ROUND(A3,2)<1,"",TEXT(INT(ROUND(A3,2)),"[dbnum2]")&"元")&IF(MOD(INT(ROUND(A3,2)*10),10)=0,IF(INT(ROUND(A3,2))*MOD(INT(ROUND(A3,2)*100),10)=0,"","零"),TEXT(MOD(INT(ROUND(A3,2)*10),10),"[dbnum2]")&"角")&IF(MOD(INT(ROUND(A3,2)*100),10)=0,"整",TEXT(MOD(INT(ROUND(A3,2)*100),10),"[dbnum2]")&"分")))
2。用自定义函数转换。代码如下：
Public Function gly1126(n)
dx = Replace(Application.Text(Round(n + 0.00000001, 2), "[DBnum2]"), ".", "元")
dx = IIf(Left(Right(dx, 3), 1) = "元", Left(dx, Len(dx) - 1) & "角" & Right(dx, 1) & "分", IIf(Left(Right(dx, 2), 1) = "元", dx & "角整", IIf(dx = "零", "", dx & "元整")))
gly1126 = Replace(Replace(Replace(Replace(dx, "零元零角", ""), "零元", ""), "零角", "零"), "-", "(负)")
End Function
说明：这代码是网友gly1126提供的，请把代码复制到VB代码窗口的相应模块中。自定义函数的使用方法与EXCEL函数的使用方法一样的，请参照使用。