如何在Excel表格中将美元小写金额转换为大写？

借用微软SpellNumber()原始VBA代码

来源：将数字转换为单词 - Excel

录入代码

新建Excel表格，如2007之前版本，存为xls，2007及以上版本请存为xlsm。启用宏。建好之后按Alt+F11（键盘上有Fn功能键的，同时需要按Alt+Fn+F11），进入菜单：插入→模块，拷贝如下修改好的代码进去，保存：

'函数1 数字转英文大写`SpellNumber()`
'函数2 判断单元格内容是否为日期 `IsDate()`禁用了
'有条件格式的`VBA`宏命令，如表格标题/字段有增减，相应的代码需要手动修改01/13/2016 13:53:12 +0800
'
Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand " '需要处理Thousand/Million/Billion/Trillion后全为0时多一个空格的情况，如1000. one thousand  only
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
                  "00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Dollars
Case ""
Dollars = "Zero"       '定义当值为0
Case "One"
Dollars = "One"        '取消默认美元显示
Case Else
Dollars = Dollars & ""       '取消默认美元显示
End Select
Select Case Cents
Case ""
Cents = ""       '末尾加only -only =LEFT(A1,LEN(A1)-1) 函数去掉多余空格，再用公式加only
Case "One"
Cents = " and Cent One"       '判断0.01时cent用单数 末尾加only -only
Case Else
Cents = " and Cents " & Cents & ""       '判断不为1美分，且有小数时cent用复数 末尾加only -only
End Select
SpellNumber = Dollars & Cents
End Function

' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
'If Mid(MyNumber, 1, 1) <> "0" Then
'Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
'End If  关键 加and
If Mid(MyNumber, 1, 1) <> "0" Then
If Mid(MyNumber, 2, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred and "   '十位有数，加单位百和and

Else
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred"  '十位没数，加单位百，去掉空格，防止整百结尾的数加only时有两空格
End If
If Mid(MyNumber, 3, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred and " '个位有数，加单位百和and
End If
End If

' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If

' Convert the tens and ones place.转换10
GetHundreds = Result
End Function

' Converts a number from 100-999 into text
'Function GetHundreds(ByVal MyNumber)
'Dim Result As String
'If Val(MyNumber) = 0 Then Exit Function
'MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
'If Mid(MyNumber, 1, 1) <> "0" Then
'Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
'End If
' Convert the tens and ones place.
'If Mid(MyNumber, 2, 1) <> "0" Then
'Result = Result & GetTens(Mid(MyNumber, 2))
'Else
'Result = Result & GetDigit(Mid(MyNumber, 3))
'End If
'GetHundreds = Result
'End Function

' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = ""           ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
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                                 ' If value between 20/30/40/50/60/70/80/90
Select Case Val(Left(TensText, 2))
Case 20: Result = "Twenty"
Case 30: Result = "Thirty"
Case 40: Result = "Forty"
Case 50: Result = "Fifty"
Case 60: Result = "Sixty"
Case 70: Result = "Seventy"
Case 80: Result = "Eighty"
Case 90: Result = "Ninety"
Case 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

End Select
Result = Result & GetDigit _
(Right(TensText, 1))  ' Retrieve ones place.
End If
GetTens = Result
End Function

' Converts a number from 1 to 9 into text.
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

引用公式

公式引用：假定数字在A1单元格，则引用公式为：=SpellNumber(A1)
在A1单元格中输入≤15位的数字，如有小数点，整数位数应≤14，总计数字不能超过15位（不含小数点），此处为98789.58，B1单元格中输入：="USD "&IF(RIGHT(UPPER(SpellNumber(A1)),1)=" ",UPPER(SpellNumber(A1))&"ONLY",UPPER(SpellNumber(A1))&" ONLY")。实际值如下：

 * A1: 98789.58
 * B1: USD NINETY-EIGHT THOUSAND SEVEN HUNDRED AND EIGHTY-NINE AND CENTS FIFTY-EIGHT ONLY

xlsm示例文件请参见：百度网盘：https://pan.baidu.com/s/1Bt9n9VuvRwm6jRtKiDHjqg (提取码：66yf)

---

相关应用文章：
邮件合并使用方法 - 简书
https://www.jianshu.com/p/6e3bfcbaafe6

如何在Excel表格中将美元小写金额转换为大写？ - 简书
https://www.jianshu.com/p/fe6d7bd3b8c9