Excel金额大写经典函数及自定义函数

 金额大写经典公式：

1.=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(A1,'[dbnum2]0拾万0万0千0百0拾0元0角0分'),'零拾万',''),'零万',''),'零千',''),'零百',''),'零拾',''),'零元',''),'零角',''),'零分','')

2.=IF(H6='','',IF(H6<0,'负','')&TEXT(TRUNC(ABS(ROUND(H6,2))),'[DBNum2]')&'元'&IF(ISERR(FIND('.',ROUND(H6,2))),'',TEXT(RIGHT(TRUNC(ROUND(H6,2)*10)),'[DBNum2]'))&IF(ISERR(FIND('.0',TEXT(H6,'0.00'))),'角','')&IF(LEFT(RIGHT(ROUND(H6,2),3))='.',TEXT(RIGHT(ROUND(H6,2)),'[DBNum2]')&'分','整'))

3.=IFERROR(IF(A1*1,SUBSTITUTE(IF(-RMB(A1*1,2),TEXT(A1*1,';负')&TEXT(INT(ABS(A1*1)+0.5%),'[dbnum2]G/通用格式元;;')&TEXT(RIGHT(RMB(A1*1,2),2),'[>9][dbnum2]0角0分;'&IF(A1^2<1,,0)&'[>][dbnum2]0分;整'),),'零分','整'),''),'非数字')

4.=IF(D43<0,'负',)&IF(D43,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(INT(ABS(D43)),'[dbnum2]G/通用格式元零;;')&IF(INT(ABS(D43))=ABS(D43),'整',TEXT(RIGHT(TEXT(ABS(D43),'.00'),2),'[dbnum2]0角0分')),'零角',''),'零分','整'),'零整','整'),'')

自定义函数

Function 大写金额(ByVal n) As String 'n as Currency

    Const cNum As String = '零壹贰叁肆伍陆柒捌玖-万仟佰拾亿仟佰拾万仟佰拾元角分'

    Const cCha As String = '零仟零佰零拾零零零零零亿零万零元亿万零角零分零整-零零零零零亿万元亿零整整'

    Dim sNum As String

    Dim i As Long

    

    If (n <> 0) And (Abs(n) < 10000000000000#) Then

        sNum = Trim(Str(Int(Abs(n) * 100)))

        For i = 1 To Len(sNum) '逐位转换

            大写金额 = 大写金额 + Mid(cNum, (Mid(sNum, i, 1)) + 1, 1) + Mid(cNum, 26 - Len(sNum) + i, 1)

        Next

        For i = 0 To 11 '去掉多余的零

            大写金额 = Replace(大写金额, Mid(cCha, i * 2 + 1, 2), Mid(cCha, i + 26, 1))

        Next

        If n < 0 Then 大写金额 = '(负)' + 大写金额

    Else

        大写金额 = IIf(n = 0, '零元整', '溢出')

    End If

End Function