金額大寫經(jīng)典公式：

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,'負(fù)','')&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,';負(fù)')&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分;整'),),'零分','整'),''),'非數(shù)字')

4.=IF(D43<0,'負(fù)',)&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分')),'零角',''),'零分','整'),'零整','整'),'')

自定義函數(shù)

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) '逐位轉(zhuǎn)換

            大寫金額 = 大寫金額 + 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 大寫金額 = '(負(fù))' + 大寫金額

    Else

        大寫金額 = IIf(n = 0, '零元整', '溢出')

    End If

End Function