excel中怎么把数字转换成大写英文货币-完美版

#excel把数字转换成大写英文货币#

在Excel中，将数字转换成大写英文货币可以通过使用Excel的函数来实现。以下是一个使用VBA（Visual Basic for Applications）宏的方法，该方法将数字转换成大写英文货币：

1. 打开Excel并按下ALT + F11打开VBA编辑器。
2. 在VBA编辑器中，选择“插入” -> “模块”以创建一个新的模块。
3. 将以下VBA代码复制粘贴到新模块中： 

Function ConvertToWords(ByVal MyNumber)
    Dim Units As String
    Dim DecimalPlace As String
    Dim TempStr As String
    Dim DecimalSeparator As String
    Dim Count As Integer
    Dim DecimalSeparatorPosition As Integer
    Dim CharCount As Integer
    Dim HundredCount As Integer
    Dim DecimalSeparatorCount As Integer

    ReDim Place(9) As String
    Place(2) = " THOUSAND "
    Place(3) = " MILLION "
    Place(4) = " BILLION "
    Place(5) = " TRILLION "

    DecimalSeparator = "."
    ReDim TempArray(1 To Len(MyNumber))

    MyNumber = Trim(CStr(MyNumber))

    DecimalSeparatorPosition = InStr(MyNumber, DecimalSeparator)

    If DecimalSeparatorPosition > 0 Then
        DecimalPlace = GetTens(Left(Mid(MyNumber, DecimalSeparatorPosition + 1) & "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalSeparatorPosition - 1))
    End If

    Count = 1
    Do While MyNumber <> ""
        TempCount = GetHundreds(Right(MyNumber, 3))
        If TempCount <> "" Then TempStr = TempCount & Place(Count) & TempStr
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop

    Select Case DecimalPlace
        Case ""
            DecimalPlace = ""
        Case Else
            DecimalPlace = " AND " & DecimalPlace & " CENTS"
    End Select

    If Len(TempStr) > 0 And Len(DecimalPlace) > 0 Then
        ConvertToWords = "SAY U.S. DOLLARS " & UCase(Trim(TempStr) & DecimalPlace & " ONLY")
    Else
        ConvertToWords = "SAY U.S. DOLLARS ONLY"
    End If

End Function

Private Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " HUNDRED "
    End If
    If Mid(MyNumber, 2, 1) <> "0" Then
        If Len(Result) > 0 Then Result = Result & "AND "
        Result = Result & GetTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
End Function

Private Function GetTens(TensText)
    Dim Result As String
    Result = ""
    If Val(Left(TensText, 1)) = 1 Then
        Select Case Val(TensText)
            Case 10: Result = "TEN"
            Case 11: Result = "ELEVEN"
            Case 12: Result = "TWELVE"
            Case 13: Result = "THIRTEEN"
            Case 14: Result = "FOURTEEN"
            Case 15: Result = "FIFTEEN"
            Case 16: Result = "SIXTEEN"
            Case 17: Result = "SEVENTEEN"
            Case 18: Result = "EIGHTEEN"
            Case 19: Result = "NINETEEN"
            Case Else
        End Select
    Else
        Select Case Val(Left(TensText, 1))
            Case 2: Result = "TWENTY "
            Case 3: Result = "THIRTY "
            Case 4: Result = "FORTY "
            Case 5: Result = "FIFTY "
            Case 6: Result = "SIXTY "
            Case 7: Result = "SEVENTY "
            Case 8: Result = "EIGHTY "
            Case 9: Result = "NINETY "
            Case Else
        End Select
        Result = Result & GetDigit(Right(TensText, 1))
    End If
    GetTens = Result
End Function

Private Function GetDigit(Digit)
    Select Case Val(Digit)
        Case 1: GetDigit = "ONE"
        Case 2: GetDigit = "TWO"
        Case 3: GetDigit = "THREE"
        Case 4: GetDigit = "FOUR"
        Case 5: GetDigit = "FIVE"
        Case 6: GetDigit = "SIX"
        Case 7: GetDigit = "SEVEN"
        Case 8: GetDigit = "EIGHT"
        Case 9: GetDigit = "NINE"
        Case Else: GetDigit = ""
    End Select
End Function

4.关闭VBA编辑器，返回到Excel工作表

5.在工作表中，你可以使用以下公式将数字转换成大写英文货币

=ConvertToWords(A1)

其中，A1是包含你要转换的数字的单元格引用。将此公式应用到你希望进行转换的单元格范围即可。

请注意，这种方法是使用VBA宏实现的，如果你的Excel设置禁用了宏，你可能需要启用宏以便使这个方法生效。

亲测完美运行，大家赶快使用起来吧

直接导入我导出的文件到宏里面也是可以的