When preparing financial reimbursement sheets or accounting reports in Microsoft Excel, converting standard numeric values into traditional Chinese capitol letters is a standard requirement. While some alternative office suites offer built-in utilities, Microsoft Office provides a more reliable and flexible approach through Visual Basic for Applications (VBA). By implementing a custom function, you can automate this conversion directly within your workbook without relying on external add-ins.

Implementing the Conversion Module

Begin by launching the Visual Basic Editor. Navigate to any spreadsheet, press Alt + F11 on your keyboard, then go to the Insert menu and select Module. This opens a blank script window where you can paste the custom conversion logic.

Function TransformToCapitalCNY(numVal As Variant) As String
    Dim intPart As String, fracPart As String
    Dim tempStr As String
    Dim i As Long, curDigit As Integer
    Dim res As String
    Dim zeroFlag As Boolean
    Dim idx As Long

    Dim numChars(0 To 9) As String
    numChars(0) = "零": numChars(1) = "壹": numChars(2) = "贰"
    numChars(3) = "叁": numChars(4) = "肆": numChars(5) = "伍"
    numChars(6) = "陆": numChars(7) = "柒": numChars(8) = "捌"
    numChars(9) = "玖"

    Dim placeVal(0 To 3) As String
    placeVal(0) = "": placeVal(1) = "拾": placeVal(2) = "佰": placeVal(3) = "仟"

    Dim majorVal(0 To 2) As String
    majorVal(0) = "": majorVal(1) = "万": majorVal(2) = "亿"

    If IsNull(numVal) Or numVal = 0 Then
        TransformToCapitalCNY = "零元整"
        Exit Function
    End If

    tempStr = Format(CDbl(numVal), "0.00")
    If InStr(tempStr, ".") > 0 Then
        intPart = Split(tempStr, ".")(0)
        fracPart = Split(tempStr, ".")(1)
    Else
        intPart = tempStr
        fracPart = "00"
    End If

    res = ""
    zeroFlag = False

    For i = 1 To Len(intPart)
        curDigit = CInt(Mid(intPart, i, 1))
        idx = Len(intPart) - i

        If curDigit = 0 Then
            zeroFlag = True
        Else
            If zeroFlag Then
                res = res & numChars(0)
                zeroFlag = False
            End If
            res = res & numChars(curDigit) & placeVal(idx Mod 4)
        End If

        If idx Mod 4 = 0 And idx > 0 Then
            If Right(res, 1) <> "零" Then
                res = res & majorVal(idx \ 4)
            Else
                res = Left(res, Len(res) - 1) & majorVal(idx \ 4)
                zeroFlag = False
            End If
        End If
    Next i

    If Right(res, 1) = "零" Then res = Left(res, Len(res) - 1)

    Dim jiaoDigit As Integer, fenDigit As Integer
    jiaoDigit = CInt(Mid(fracPart, 1, 1))
    fenDigit = CInt(Mid(fracPart, 2, 1))

    If jiaoDigit = 0 And fenDigit = 0 Then
        TransformToCapitalCNY = res & "元整"
    Else
        res = res & "元"
        If jiaoDigit > 0 Then
            res = res & numChars(jiaoDigit) & "角"
        Else
            If fenDigit > 0 Then res = res & "零"
        End If
        If fenDigit > 0 Then
            res = res & numChars(fenDigit) & "分"
        End If
        TransformToCapitalCNY = res
    End If
End Function

Applying the Function in Worksheets

Once the script is saved, close the VBA editor to return to your Excel interface. The custom function is now registered and available for use in any formula cell. To convert a numeric value located in a specific cell, simply enter the function name followed by the cell reference inside parentheses.

For example, if your original amount is stored in cell D5, input the following formula into an adjacent cell:

=TransformToCapitalCNY(D5)

Press Enter to execute. The cell will instantly display the formatted capital string, accurately handling integer placements, decimal fractions, and zero suppression according to standard accounting conventions. You can drag the fill handle downward to apply this transformation across an entire column of financial data.