EXCEL 中如何将数字转换成英文_excel中怎么转换数字

EXCEL 中如何将数字转换成英文由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“excel中怎么转换数字”。

一、网上下载的代码

1.创建一个模块：

在SHEET上，右键－》查看代码。选中“模块”－》插入模块。

2.写代码：

Option Explicit Dim StrNO(19)As String Dim Unit(8)As String Dim StrTens(9)As String Public 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 Init Str = CStr(Round(Number, 2))' Str = Number If InStr(1, Str, “.”)= 0 Then BeforePoint = Str AfterPoint = “” Else BeforePoint = Left(Str, InStr(1, Str, “.”)InStr(1, Str, “.”))If Len(T)2 Then AfterPoint = Val(Left(T, 2))End If If 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(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)3))If BeforePoint = String(Len(BeforePoint), “0”)Then Exit Do 'Debug.Print Str Loop BeforePoint = Str If Len(AfterPoint)> 0 Then AfterPoint =Unit(6)& “ ” & 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)0 Then DecodeHundred = StrNO(tmp)Case 2 tmp = CInt(HundredString)If tmp 0 Then If(tmp 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 If End 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）就可以拉！很简单的吧？

二、以川改过的代码：

'功能模块：数字转英文(货币)大写

'Public Function NumberToString(Number As Double)As String '调用形式：debug.print NumberToString(1234.32)'说明：最大支持12位数字，小数点后精确两位 '程序：杨鑫光（Volitation）Dim StrNO(19)As String Dim Unit(8)As String Dim StrTens(9)As String

Public 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 Call Init '//开始处理

Str = CStr(Round(Number, 2))' Str = Number If InStr(1, Str, “.”)= 0 Then BeforePoint = Str AfterPoint = “” Else BeforePoint = Left(Str, InStr(1, Str, “.”)InStr(1, Str, “.”))End If

If 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(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)3))If BeforePoint = String(Len(BeforePoint), “0”)Then Exit Do 'Debug.Print Str Loop BeforePoint = Str

NumberToString = BeforePoint & “ ” End Function Private Function DecodeHundred(HundredString As String)As String Dim tmp As Integer If Len(HundredString)> 0 And Len(HundredString)0 Then DecodeHundred = StrNO(tmp)Case 2 tmp = CInt(HundredString)If tmp 0 Then If(tmp 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 If

End 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(8)= “And” End If End Sub

三、是我改过的代码：

Option Explicit Dim StrNO(19)As String Dim Unit(8)As String Dim StrTens(9)As String Public 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 Init

Str = CStr(Round(Number, 2))

' Str = Number

If InStr(1, Str, “.”)= 0 Then

BeforePoint = Str

AfterPoint = “”

Else

BeforePoint = Left(Str, InStr(1, Str, “.”)InStr(1, Str, “.”))

If Len(T)

If Len(T)= 2 Then AfterPoint = Val(T)

If Len(T)> 2 Then AfterPoint = Val(Left(T, 2))

End If

If 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(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)3))

If BeforePoint = String(Len(BeforePoint), “0”)Then Exit Do

'Debug.Print Str

Loop

BeforePoint = Str

If Len(AfterPoint)> 0 Then

AfterPoint = Unit(6)& “ ” & DecodeHundred(AfterPoint)

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)

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

DecodeHundred = StrNO(tmp)

Else

If CInt(Right(HundredString, 1))= 0 Then

DecodeHundred = StrTens(Int(tmp / 10))

Else

DecodeHundred = StrTens(Int(tmp / StrNO(CInt(Right(HundredString, 1)))

End If

End If

10))& “-” &

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 If End 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