财务部提出在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, ".") - 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)就可以拉!很简单的吧?