Convert Currency Amounts to Words in Excel: CNY, USD, GBP, HKD, JPY and EUR

For a single-cell input such as USD1500.00, read the first three characters as the currency code and the remaining characters as the amount. The task is to convert one combined Excel text value into amounts in words for CNY, USD, GBP, HKD, JPY, or EUR without splitting the input into two cells.

Excel’s built-in currency formatting is only display formatting. Microsoft documents currency display separately from text conversion: format numbers as currency in Excel. The MID and TEXT functions help parse and format text, but spelling a currency amount needs a custom rule.

Parse the combined input first

Need	Formula	Result for USD1500.00
Currency code	=UPPER(LEFT(TRIM(A1),3))	USD
Amount text	=MID(TRIM(A1),4,LEN(TRIM(A1))-3)	1500.00
Formatted display only	=UPPER(LEFT(TRIM(A1),3))&" "&TEXT(--MID(TRIM(A1),4,LEN(TRIM(A1))-3),"#,##0.00")	USD 1,500.00

Those formulas do not spell the amount. Use the user-defined function below when you need words. The English currency output is Title Case, such as One Thousand Five Hundred US Dollars. If you need all capital letters, wrap the function with UPPER, for example =UPPER(CurrencyAmountWords(A1)).

VBA user-defined function for six currency codes

The function accepts text such as CNY315.05, USD1500.00, GBP1.01, HKD12.30, JPY1500.50, or EUR0. CNY returns a custom RMB financial-style numeral wording; the other supported codes return English amount words. It does not convert exchange rates.

Installation in desktop Excel: press Alt+F11, choose Insert > Module, paste the code into a standard module, save as .xlsm, and use =CurrencyAmountWords(A1).

Option Explicit

Public Function CurrencyAmountWords(ByVal CodeAndAmount As Variant) As Variant
    On Error GoTo Fail
    Dim s As String, code As String, amountText As String
    Dim decimals As Long, neg As Boolean, roundedMinor As String
    Dim majorText As String, minorText As String
    Dim errMsg As String

    If IsError(CodeAndAmount) Then
        CurrencyAmountWords = CVErr(xlErrValue)
        Exit Function
    End If

    s = Trim(CStr(CodeAndAmount))
    If Len(s) < 4 Then
        CurrencyAmountWords = CVErr(xlErrValue)
        Exit Function
    End If

    code = UCase(Left(s, 3))
    amountText = Mid(s, 4)

    Select Case code
        Case "CNY", "USD", "GBP", "HKD", "EUR"
            decimals = 2
        Case "JPY"
            decimals = 0
        Case Else
            CurrencyAmountWords = CVErr(xlErrValue)
            Exit Function
    End Select

    roundedMinor = RoundAmountTextToMinor(amountText, decimals, neg, errMsg)
    If errMsg <> "" Then
        If errMsg = "NUM" Then
            CurrencyAmountWords = CVErr(xlErrNum)
        Else
            CurrencyAmountWords = CVErr(xlErrValue)
        End If
        Exit Function
    End If

    SplitMinor roundedMinor, decimals, majorText, minorText
    If Len(StripLeadingZeroes(majorText)) > 15 Then
        CurrencyAmountWords = CVErr(xlErrNum)
        Exit Function
    End If
    If StripLeadingZeroes(roundedMinor) = "0" Then neg = False

    If code = "CNY" Then
        CurrencyAmountWords = RMBWords(majorText, minorText, neg)
    Else
        CurrencyAmountWords = EnglishCurrencyWords(code, majorText, minorText, neg)
    End If
    Exit Function

Fail:
    CurrencyAmountWords = CVErr(xlErrValue)
End Function

Private Function RoundAmountTextToMinor(ByVal amountText As String, ByVal decimals As Long, ByRef neg As Boolean, ByRef errMsg As String) As String
    Dim s As String, i As Long, ch As String
    Dim p As Long, intPart As String, fracPart As String
    Dim keepFrac As String, nextDigit As String, combined As String

    s = Trim(amountText)
    If s = "" Then errMsg = "VALUE": Exit Function

    If Left(s, 1) = "-" Then
        neg = True
        s = Mid(s, 2)
    ElseIf Left(s, 1) = "+" Then
        s = Mid(s, 2)
    End If
    If s = "" Then errMsg = "VALUE": Exit Function

    p = InStr(1, s, ".", vbBinaryCompare)
    If p > 0 Then
        intPart = Left(s, p - 1)
        fracPart = Mid(s, p + 1)
        If InStr(p + 1, s, ".", vbBinaryCompare) > 0 Then errMsg = "VALUE": Exit Function
    Else
        intPart = s
        fracPart = ""
    End If

    If intPart = "" And fracPart = "" Then errMsg = "VALUE": Exit Function
    If intPart = "" Then intPart = "0"
    For i = 1 To Len(intPart)
        ch = Mid(intPart, i, 1)
        If ch < "0" Or ch > "9" Then errMsg = "VALUE": Exit Function
    Next i
    For i = 1 To Len(fracPart)
        ch = Mid(fracPart, i, 1)
        If ch < "0" Or ch > "9" Then errMsg = "VALUE": Exit Function
    Next i

    intPart = StripLeadingZeroes(intPart)
    If Len(intPart) > 15 Then errMsg = "NUM": Exit Function

    If decimals = 0 Then
        combined = intPart
        nextDigit = Left(fracPart & "0", 1)
    Else
        keepFrac = Left(fracPart & String(decimals, "0"), decimals)
        nextDigit = Mid(fracPart & String(decimals + 1, "0"), decimals + 1, 1)
        combined = intPart & keepFrac
    End If

    combined = StripLeadingZeroes(combined)
    If nextDigit >= "5" Then combined = AddOneToDigitString(combined)
    If Len(combined) > 15 + decimals Then errMsg = "NUM": Exit Function

    RoundAmountTextToMinor = combined
End Function

Private Sub SplitMinor(ByVal roundedMinor As String, ByVal decimals As Long, ByRef majorText As String, ByRef minorText As String)
    roundedMinor = StripLeadingZeroes(roundedMinor)
    If decimals = 0 Then
        majorText = roundedMinor
        minorText = ""
    Else
        If Len(roundedMinor) <= decimals Then
            majorText = "0"
            minorText = Right(String(decimals, "0") & roundedMinor, decimals)
        Else
            majorText = Left(roundedMinor, Len(roundedMinor) - decimals)
            minorText = Right(roundedMinor, decimals)
        End If
    End If
End Sub

Private Function StripLeadingZeroes(ByVal s As String) As String
    Do While Len(s) > 1 And Left(s, 1) = "0"
        s = Mid(s, 2)
    Loop
    If s = "" Then s = "0"
    StripLeadingZeroes = s
End Function

Private Function AddOneToDigitString(ByVal s As String) As String
    Dim i As Long, carry As Long, d As Long, out As String
    carry = 1
    For i = Len(s) To 1 Step -1
        d = CLng(Mid(s, i, 1)) + carry
        If d = 10 Then
            out = "0" & out
            carry = 1
        Else
            out = CStr(d) & out
            carry = 0
        End If
    Next i
    If carry = 1 Then out = "1" & out
    AddOneToDigitString = out
End Function

Private Function RMBWords(ByVal majorText As String, ByVal minorText As String, ByVal neg As Boolean) As String
    Dim digits As Variant, jiao As Long, fen As Long, prefix As String
    digits = Array("零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖")
    If neg Then prefix = "负"

    If Len(minorText) < 2 Then minorText = minorText & String(2 - Len(minorText), "0")
    jiao = CLng(Mid(minorText, 1, 1))
    fen = CLng(Mid(minorText, 2, 1))

    RMBWords = "人民币" & prefix & ChineseIntegerWords(majorText) & "元"
    If jiao = 0 And fen = 0 Then
        RMBWords = RMBWords & "整"
    Else
        If jiao > 0 Then RMBWords = RMBWords & digits(jiao) & "角"
        If jiao = 0 And fen > 0 Then RMBWords = RMBWords & "零"
        If fen > 0 Then RMBWords = RMBWords & digits(fen) & "分"
    End If
End Function

Private Function ChineseIntegerWords(ByVal nText As String) As String
    Dim groupUnits As Variant, groups() As String, gCount As Long
    Dim s As String, part As String, i As Long, needZero As Boolean

    groupUnits = Array("", "万", "亿", "兆")
    nText = StripLeadingZeroes(nText)
    If nText = "0" Then ChineseIntegerWords = "零": Exit Function

    Do While Len(nText) > 0
        ReDim Preserve groups(gCount)
        If Len(nText) > 4 Then
            groups(gCount) = Right(nText, 4)
            nText = Left(nText, Len(nText) - 4)
        Else
            groups(gCount) = nText
            nText = ""
        End If
        gCount = gCount + 1
    Loop

    For i = gCount - 1 To 0 Step -1
        part = ChineseFourDigits(groups(i))
        If part = "" Then
            If s <> "" Then needZero = True
        Else
            If s <> "" And (needZero Or CLng(groups(i)) < 1000) Then s = s & "零"
            s = s & part & groupUnits(i)
            needZero = False
        End If
    Next i
    ChineseIntegerWords = s
End Function

Private Function ChineseFourDigits(ByVal groupText As String) As String
    Dim digits As Variant, units As Variant, s As String
    Dim padded As String, i As Long, d As Long, pos As Long, zeroPending As Boolean
    digits = Array("零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖")
    units = Array("仟", "佰", "拾", "")
    padded = Right("0000" & groupText, 4)
    For i = 1 To 4
        d = CLng(Mid(padded, i, 1))
        pos = i - 1
        If d = 0 Then
            If s <> "" Then zeroPending = True
        Else
            If zeroPending Then s = s & "零": zeroPending = False
            s = s & digits(d) & units(pos)
        End If
    Next i
    ChineseFourDigits = s
End Function

Private Function EnglishCurrencyWords(ByVal code As String, ByVal majorText As String, ByVal minorText As String, ByVal neg As Boolean) As String
    Dim majorSingular As String, majorPlural As String, minorSingular As String, minorPlural As String
    Dim majorWords As String, minorVal As Long, prefix As String

    Select Case code
        Case "USD": majorSingular = "US Dollar": majorPlural = "US Dollars": minorSingular = "Cent": minorPlural = "Cents"
        Case "GBP": majorSingular = "British Pound": majorPlural = "British Pounds": minorSingular = "Penny": minorPlural = "Pence"
        Case "HKD": majorSingular = "Hong Kong Dollar": majorPlural = "Hong Kong Dollars": minorSingular = "Cent": minorPlural = "Cents"
        Case "EUR": majorSingular = "Euro": majorPlural = "Euros": minorSingular = "Cent": minorPlural = "Cents"
        Case "JPY": majorSingular = "Japanese Yen": majorPlural = "Japanese Yen": minorSingular = "": minorPlural = ""
    End Select

    If neg Then prefix = "Negative "
    majorWords = EnglishIntegerWords(majorText)

    If code = "JPY" Then
        EnglishCurrencyWords = prefix & majorWords & " " & majorPlural
        Exit Function
    End If

    If Len(minorText) < 2 Then minorText = minorText & String(2 - Len(minorText), "0")
    minorVal = CLng(minorText)

    EnglishCurrencyWords = prefix & majorWords & " " & IIf(majorText = "1", majorSingular, majorPlural)
    If minorVal = 0 Then
        EnglishCurrencyWords = EnglishCurrencyWords & " and No " & minorPlural
    Else
        EnglishCurrencyWords = EnglishCurrencyWords & " and " & EnglishIntegerWords(CStr(minorVal)) & " " & IIf(minorVal = 1, minorSingular, minorPlural)
    End If
End Function

Private Function EnglishIntegerWords(ByVal nText As String) As String
    Dim scales As Variant, parts() As String, pCount As Long
    Dim chunk As String, words As String, i As Long
    scales = Array("", "Thousand", "Million", "Billion", "Trillion")
    nText = StripLeadingZeroes(nText)
    If nText = "0" Then EnglishIntegerWords = "Zero": Exit Function

    Do While Len(nText) > 0
        ReDim Preserve parts(pCount)
        If Len(nText) > 3 Then
            parts(pCount) = Right(nText, 3)
            nText = Left(nText, Len(nText) - 3)
        Else
            parts(pCount) = nText
            nText = ""
        End If
        pCount = pCount + 1
    Loop

    For i = pCount - 1 To 0 Step -1
        If CLng(parts(i)) > 0 Then
            chunk = EnglishUnder1000(CLng(parts(i)))
            If scales(i) <> "" Then chunk = chunk & " " & scales(i)
            If words <> "" Then words = words & " "
            words = words & chunk
        End If
    Next i
    EnglishIntegerWords = words
End Function

Private Function EnglishUnder1000(ByVal n As Long) As String
    Dim ones As Variant, teens As Variant, tens As Variant
    Dim h As Long, r As Long, t As Long, o As Long, s As String
    ones = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine")
    teens = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
    tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
    h = n \ 100
    r = n Mod 100
    If h > 0 Then s = ones(h) & " Hundred"
    If r >= 10 And r <= 19 Then
        If s <> "" Then s = s & " "
        s = s & teens(r - 10)
    Else
        t = r \ 10
        o = r Mod 10
        If t > 0 Then
            If s <> "" Then s = s & " "
            s = s & tens(t)
        End If
        If o > 0 Then
            If s <> "" Then s = s & " "
            s = s & ones(o)
        End If
    End If
    EnglishUnder1000 = s
End Function

Code boundaries and edge-case checks

The rounding step treats the amount as text digits, not as CDbl(amount)*100. That is why CNY1.005 is rounded by the third decimal digit to 人民币壹元零壹分 instead of depending on binary floating-point representation. The integer part is limited to 15 digits after rounding; larger values return #NUM!. Unsupported codes, blank amounts, nonnumeric amounts, and Excel error inputs return #VALUE!. A negative amount that rounds to zero becomes zero, not negative zero.

For CNY, this implementation uses 兆 as 10^12. It is a custom financial-style number wording for worksheet use, not a guarantee that every bank, invoice platform, or contract reviewer will accept the exact same wording. If the output will be used for payment, reimbursement, or legal documents, confirm the required wording style with the receiving institution.

Input	Expected output or error	Boundary covered
CNY0	人民币零元整	zero
CNY10001	人民币壹万零壹元整	zero inside 万 grouping
CNY100000001	人民币壹亿零壹元整	亿 group gap
CNY100010001	人民币壹亿零壹万零壹元整	multiple group gaps
CNY1.005	人民币壹元零壹分	decimal text rounding
CNY-0.004	人民币零元整	negative tiny value rounds to zero
USD1500.00	One Thousand Five Hundred US Dollars and No Cents	USD plural and cents
GBP1.01	One British Pound and One Penny	singular pound/penny
HKD12.30	Twelve Hong Kong Dollars and Thirty Cents	HKD cents
EUR0	Zero Euros and No Cents	zero euro value
JPY1500.49	One Thousand Five Hundred Japanese Yen	JPY whole-yen rounding down
JPY1500.50	One Thousand Five Hundred One Japanese Yen	JPY whole-yen rounding up
CNY999999999999999.99	人民币玖佰玖拾玖兆玖仟玖佰玖拾玖亿玖仟玖佰玖拾玖万玖仟玖佰玖拾玖元玖角玖分	largest accepted 15-digit integer part
CNY1000000000000000	#NUM!	too large
ABC12	#VALUE!	unsupported code
CNY	#VALUE!	blank amount

These expected results were checked by reading the algorithm and by a separate reasoning mirror of the string rules. They are not presented as proof that the VBA ran in this environment. Before using the function for invoices or contracts, paste it into your own workbook and test the exact examples your organization needs.