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）就可以拉！很簡單的吧？