老师你好,怎样把excel表格里面的数字转换成英文字母?
![](/wenda/images/newWd/search.png)
问题已解决
所属话题:
#Excel#
![](http://member.chinaacc.com/homes/resources/images/home/avatar/1.jpg)
84785011 | 提问时间:2023 02/14 16:01
你好
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 Init Str = 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 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 - 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))) %26 Unit(8) %26 %26 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) %26 %26 tmpStr
End If
End If If nBit = 0 Then
Str = Trim(Str %26 %26 tmpStr)
Else
Str = Trim(Str %26 %26 tmpStr %26 %26 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
***.print Str
Loop
BeforePoint = Str If Len(AfterPoint) > 0 Then
AfterPoint = Unit(8) %26 %26 Unit(7) %26 %26 DecodeHundred(AfterPoint) %26 %26 Unit(5)
Else
AfterPoint = Unit(5)
End If
NumberToString = BeforePoint %26 %26 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)) %26 - %26 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))) %26 %26 Unit(4) %26 %26 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)就可以拉!
2023 02/14 16:02
相关问答
查看更多最新问答
查看更多