经验直达：
- 如何在EXCEL中将数字转换成英文字母
- EXCEL数字转英语公式
一、如何在EXCEL中将数字转换成英文字母
1.创建一个模块： 在SHEET上，右键－》查看代码 。选中“模块”－》插入模块 。2.写代码：Option Explicit
Dim StrNO(19) As String
Dim Unit(8) As String
Dim StrTens(9) As StringPublic Function NumberToString(Number As Double) As String
Dim Str As String, BeforePoint As String, AfterPoint As String, tmpStr As String
Dim Point As Integer
Dim nBit As Integer
Dim CurString As String
Dim nNumLen As Integer
Dim T As String
Call InitStr = CStr(Round(Number, 2))
' Str = Number
If InStr(1, Str, ".") = 0 Then
BeforePoint = Str
AfterPoint = ""
Else
BeforePoint = Left(Str, InStr(1, Str, ".") - 1)
T = Right(Str, Len(Str) - InStr(1, Str, "."))
If Len(T) < 2 Then AfterPoint = Val(T) * 10
If Len(T) = 2 Then AfterPoint = Val(T)
If Len(T) > 2 Then AfterPoint = Val(Left(T, 2))
End IfIf Len(BeforePoint) > 12 Then
NumberToString = "Too Big."
Exit Function
End If
Str = ""
Do While Len(BeforePoint) > 0
nNumLen = Len(BeforePoint)
If nNumLen Mod 3 = 0 Then
CurString = Left(BeforePoint, 3)
BeforePoint = Right(BeforePoint, nNumLen - 3)
Else
CurString = Left(BeforePoint, (nNumLen Mod 3))
BeforePoint = Right(BeforePoint, nNumLen - (nNumLen Mod 3))
End If
nBit = Len(BeforePoint) / 3
tmpStr = DecodeHundred(CurString)
If (BeforePoint = String(Len(BeforePoint), "0") Or nBit = 0) And Len(CurString) = 3 Then
If CInt(Left(CurString, 1)) <> 0 And CInt(Right(CurString, 2)) <> 0 Then
'tmpStr = Left(tmpStr, InStr(1, tmpStr, Unit(4))Len(Unit(4))) & Unit(8) & " " & Right(tmpStr, Len(tmpStr) - (InStr(1, tmpStr, Unit(4))Len(Unit(4))))
Else 'If CInt(Left(CurString, 1)) <> 0 And CInt(Right(CurString, 2)) = 0 Then
'tmpStr = Unit(8) & " " & tmpStr
End If
End IfIf nBit = 0 Then
Str = Trim(Str & " " & tmpStr)
Else
Str = Trim(Str & " " & tmpStr & " " & Unit(nBit))
End If
If Left(Str, 3) = Unit(8) Then Str = Trim(Right(Str, Len(Str) - 3))
If BeforePoint = String(Len(BeforePoint), "0") Then Exit Do
'Debug.Print Str
Loop
BeforePoint = StrIf Len(AfterPoint) > 0 Then
AfterPoint = Unit(8) & " " & Unit(7) & " " & DecodeHundred(AfterPoint) & " " & Unit(5)
Else
AfterPoint = Unit(5)
End If
NumberToString = BeforePoint & " " & AfterPoint
End Function
Private Function DecodeHundred(HundredString As String) As String
Dim tmp As Integer
If Len(HundredString) > 0 And Len(HundredString) <= 3 Then
Select Case Len(HundredString)
Case 1
tmp = CInt(HundredString)
If tmp <> 0 Then DecodeHundred = StrNO(tmp)
Case 2
tmp = CInt(HundredString)
If tmp <> 0 Then
If (tmp < 20) Then
DecodeHundred = StrNO(tmp)
Else
If CInt(Right(HundredString, 1)) = 0 Then
DecodeHundred = StrTens(Int(tmp / 10))
Else
DecodeHundred = StrTens(Int(tmp / 10)) & "-" & StrNO(CInt(Right(HundredString, 1)))
End If
End If
End If
Case 3
If CInt(Left(HundredString, 1)) <> 0 Then
DecodeHundred = StrNO(CInt(Left(HundredString, 1))) & " " & Unit(4) & " " & DecodeHundred(Right(HundredString, 2))
Else
DecodeHundred = DecodeHundred(Right(HundredString, 2))
End If
Case Else
End Select
End IfEnd Function
Private Sub Init()
If StrNO(1) <> "One" Then
StrNO(1) = "One"
StrNO(2) = "Two"
StrNO(3) = "Three"
StrNO(4) = "Four"
StrNO(5) = "Five"
StrNO(6) = "Six"
StrNO(7) = "Seven"
StrNO(8) = "Eight"
StrNO(9) = "Nine"
StrNO(10) = "Ten"
StrNO(11) = "Eleven"
StrNO(12) = "Twelve"
StrNO(13) = "Thirteen"
StrNO(14) = "Fourteen"
StrNO(15) = "Fifteen"
StrNO(16) = "Sixteen"
StrNO(17) = "Seventeen"
StrNO(18) = "Eighteen"
StrNO(19) = "Nineteen"StrTens(1) = "Ten"
StrTens(2) = "Twenty"
StrTens(3) = "Thirty"
StrTens(4) = "Forty"
StrTens(5) = "Fifty"
StrTens(6) = "Sixty"
StrTens(7) = "Seventy"
StrTens(8) = "Eighty"
StrTens(9) = "Ninety"Unit(1) = "Thousand" '材?熌??
Unit(2) = "Million" '材?熌??
Unit(3) = "Billion" '材?熌??
Unit(4) = "Hundred"
Unit(5) = "Only"
Unit(6) = "Point"
Unit(7) = "Cents"
Unit(8) = "And"
End If
End Sub保存此代码到本地3.模块中已经定义了函数名称：NumberToString直接当作EXCEL本地函数使用，例如在A1=7,在B1中输入＝NumberToString（A1）就可以拉！
二、EXCEL数字转英语公式
1.新建如图所示的Excel表格，把A列中的数字用英文的形式转换到B列中 。
2.按住“Alt F11”打开VBA编辑器，如图所示 。
3. 在VBA编辑器中单击菜单栏“插入”——模块，如图所示 。
4.在打开的模块中输入如下代码：
Option Explicit
【EXCEL数字转英语公式 如何在EXCEL中将数字转换成英文字母】Function 数字转英文(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
MyNumber = Trim(Str(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace > 0 Then
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace1) & _
"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 = Count1
Loop
Select Case Dollars
Case ""
Dollars = "No Dollars"
Case "One"
Dollars = "One Dollar"
Case Else
Dollars = Dollars & " Dollars"
End Select
Select Case Cents
Case ""
Cents = " and No Cents"
Case "One"
Cents = " and One Cent"
Case Else
Cents = " and " & Cents & " Cents"
End Select
数字转英文 = Dollars & Cents
End Function
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
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function
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
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
5.调用函数：
现在回到Excel表格中 ， 单击“B1”单元格 ， 在菜单栏选择“插入”——函数 。
6.在打开的“插入函数”对话框的“或选择类别”中选择“用户定义”，然后选择函数“数字转英文” ， 单击“确定”按钮 。
7.在打开的“函数参数”对话框中输入“A1”，单击“确定”按钮 。
8.然后用填充手柄向下填充公式，现在就可以看到转换好的英文了 。
