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, ".") - 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）就可以拉！很简单的吧？