利用自定义函数SpellNumber()来用Excell转换数字为英文（俗称英文大写）的方法
2014-09-16 22:54阅读：
参考：如何在 Excel 中将数值转换为英文单词
http://support.microsoft.com/kb/213360并做修改如下：
启动 Microsoft Excel。
按 Alt+F11 启动 Visual Basic 编辑器。
在“插入”菜单上，单击“模块”。
在模块表中键入下面的代码：
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 = ''
Case 'One'
Cents = ' and Cent One'
Case Else
Cents = ' and Cents ' & Cents
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单元格，在启用宏的情况下，在引用单元格B1输入：
=SpellNumber(A1)
即可引用。
变成美元格式的引用方法，则B1为：
='USD'&'
'&UPPER(SpellNumber(A1))
上述自定义函数中，因整百等单位会多出一个空格，可以添加辅助列/单元格去掉对应空格。假设B1为辅助单元格，最终B2单元格公式为：
=IF(RIGHT(B1,1)=' ',B1&'ONLY',B1&'
ONLY')
或
=IF(RIGHT(B1,1)=' ',LEFT(B1,LEN(B1)-1)&'
ONLY',B1&' ONLY')
本公式支持小数。
修改的Excel文件分别见微盘：
英文大写数字微软官网(修改版).xls
(Excel2003/2007/2013适用)
http://t.cn/RhX3mcN
英文大写数字微软官网(修改版).xlsm (Excel2013适用)
http://t.cn/RhX3nOI
