如何将EXCEL中的数字转换成字母

将输入的数字

1，2，3，4，12，13，14，23，24，34，123，124，134，234，1234

转换为

A，B，C，D，AB，AC，AD，BC，BD，CD，ABC，ABD，ACD，BCD，,ABCD

显示，最好是全部写出函数。

例如：

在指定单元格里输入3，则在相应的单元格里显示C，而输入1234时，显示ABCD。

不要这么复杂吧

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,"A"),2,"B"),3,"C"),4,"D")

向下复制

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, ".") - 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))) & 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 If

If 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 = Str

If 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 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）就可以拉！

如果只想实现在A1输入3，在B1显示C，那B1的公式为：

=CHAr(LEFt(A1,1)+64)&IF(LEN(A1)>1,CHAr(MId(A1,2,1)+64),"")&IF(LEN(A1)>2,CHAr(MId(A1,3,1)+64),"")&IF(LEN(A1)>3,CHAr(MId(A1,4,1)+64),"")

如果位数会超过4，再接着增加&IF(LEN(A1)>4,CHAr(MId(A1,5,1)+64),"")。......

如果想在原单元格实现，即A1输入3，A1直接变成C，得用宏。如果你需要，再找我。