如何在Excel中快速將數字轉換為英文單詞

發布:未知文本批量工具

如何在Excel中快速將數字轉換為英文單詞？

如果您在工作表中有一列數字值，現在需要將它們設置為等效的英語單詞或英語貨幣單詞，如下面的屏幕截圖所示。在這里，我將討論如何快速，輕松地將數字更改為英語單詞。

使用用戶定義的功能將數字轉換為英文單詞

使用用戶定義的功能將數字轉換為貨幣詞

使用有用的功能將數字轉換為貨幣單詞

使用用戶定義的功能將數字轉換為英文單詞

沒有任何功能或公式可以幫助您直接在Excel中解決此任務，但是您可以創建一個用戶定義函數來完成該任務。請執行以下步驟：

1. 按住 ALT + F11 鍵，然后打開 Microsoft Visual Basic應用程序窗口.

2。點擊插頁 > 模塊，然后將以下代碼粘貼到 模塊窗口.

Function NumberstoWords(ByVal MyNumber)Update by ExtendofficeDim xStr As StringDim xFNum As IntegerDim xStrPointDim xStrNumberDim xPoint As StringDim xNumber As StringDim xP() As VariantDim xDPDim xCnt As IntegerDim xResult, xT As StringDim xLen As IntegerOn Error Resume NextxP = Array("", "Thousand ", "Million ", "Billion ", "Trillion ", " ", " ", " ", " ")xNumber = Trim(Str(MyNumber))xDP = InStr(xNumber, ".")xPoint = ""xStrNumber = ""If xDP > 0 ThenxPoint = " point "xStr = Mid(xNumber, xDP + 1)xStrPoint = Left(xStr, Len(xNumber) - xDP)For xFNum = 1 To Len(xStrPoint)xStr = Mid(xStrPoint, xFNum, 1)xPoint = xPoint & GetDigits(xStr) & " "Next xFNumxNumber = Trim(Left(xNumber, xDP - 1))End IfxCnt = 0xResult = ""xT = ""xLen = 0xLen = Int(Len(Str(xNumber)) / 3)If (Len(Str(xNumber)) Mod 3) = 0 Then xLen = xLen - 1Do While xNumber <> ""If xLen = xCnt ThenxT = GetHundredsDigits(Right(xNumber, 3), False)ElseIf xCnt = 0 ThenxT = GetHundredsDigits(Right(xNumber, 3), True)ElsexT = GetHundredsDigits(Right(xNumber, 3), False)End IfEnd IfIf xT <> "" ThenxResult = xT & xP(xCnt) & xResultEnd IfIf Len(xNumber) > 3 ThenxNumber = Left(xNumber, Len(xNumber) - 3)ElsexNumber = ""End IfxCnt = xCnt + 1LoopxResult = xResult & xPointNumberstoWords = xResultEnd FunctionFunction GetHundredsDigits(xHDgt, xB As Boolean)Dim xRStr As StringDim xStrNum As StringDim xStr As StringDim xI As IntegerDim xBB As BooleanxStrNum = xHDgtxRStr = ""On Error Resume NextxBB = TrueIf Val(xStrNum) = 0 Then Exit FunctionxStrNum = Right("000" & xStrNum, 3)xStr = Mid(xStrNum, 1, 1)If xStr <> "0" ThenxRStr = GetDigits(Mid(xStrNum, 1, 1)) & "Hundred "ElseIf xB ThenxRStr = "and "xBB = FalseElsexRStr = " "xBB = FalseEnd IfEnd IfIf Mid(xStrNum, 2, 2) <> "00" ThenxRStr = xRStr & GetTenDigits(Mid(xStrNum, 2, 2), xBB)End IfGetHundredsDigits = xRStrEnd FunctionFunction GetTenDigits(xTDgt, xB As Boolean)Dim xStr As StringDim xI As IntegerDim xArr_1() As VariantDim xArr_2() As VariantDim xT As BooleanxArr_1 = Array("Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen ")xArr_2 = Array("", "", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety ")xStr = ""xT = TrueOn Error Resume NextIf Val(Left(xTDgt, 1)) = 1 ThenxI = Val(Right(xTDgt, 1))If xB Then xStr = "and "xStr = xStr & xArr_1(xI)ElsexI = Val(Left(xTDgt, 1))If Val(Left(xTDgt, 1)) > 1 ThenIf xB Then xStr = "and "xStr = xStr & xArr_2(Val(Left(xTDgt, 1)))xT = FalseEnd IfIf xStr = "" ThenIf xB ThenxStr = "and "End IfEnd IfIf Right(xTDgt, 1) <> "0" ThenxStr = xStr & GetDigits(Right(xTDgt, 1))End IfEnd IfGetTenDigits = xStrEnd FunctionFunction GetDigits(xDgt)Dim xStr As StringDim xArr_1() As VariantxArr_1 = Array("Zero ", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ")xStr = ""On Error Resume NextxStr = xArr_1(Val(xDgt))GetDigits = xStrEnd FunctionCopy

3。保存并關閉此代碼，然后返回工作表，在空白單元格中輸入此公式 = NumberstoWords（A2）( A2 是您要將數字轉換為英文單詞的單元格），請參見屏幕截圖：

4。然后按輸入鍵，然后選擇單元格C2，然后將填充手柄拖動到要包含此公式的范圍。所有數字值均已轉換為其相應的英語單詞。

備注：此代碼僅適用于整數，但不適用于十進制數字。

使用用戶定義的功能將數字轉換為貨幣詞

如果要將數字轉換為英語貨幣單詞，則應應用下面的VBA代碼。

1. 按住 ALT + F11 鍵，然后打開 Microsoft Visual Basic應用程序窗口.

2。點擊插頁 > 模塊，然后將以下代碼粘貼到 模塊窗口.

Function SpellNumberToEnglish(ByVal pNumber)Update by ExtendofficeDim Dollars, Centsarr = Array("", "", " Thousand ", " Million ", " Billion ", " Trillion ")pNumber = Trim(Str(pNumber))xDecimal = InStr(pNumber, ".")If xDecimal > 0 ThenCents = GetTens(Left(Mid(pNumber, xDecimal + 1) & "00", 2))pNumber = Trim(Left(pNumber, xDecimal - 1))End IfxIndex = 1Do While pNumber <> ""xHundred = ""xValue = Right(pNumber, 3)If Val(xValue) <> 0 ThenxValue = Right("000" & xValue, 3)If Mid(xValue, 1, 1) <> "0" ThenxHundred = GetDigit(Mid(xValue, 1, 1)) & " Hundred "End IfIf Mid(xValue, 2, 1) <> "0" ThenxHundred = xHundred & GetTens(Mid(xValue, 2))ElsexHundred = xHundred & GetDigit(Mid(xValue, 3))End IfEnd IfIf xHundred <> "" ThenDollars = xHundred & arr(xIndex) & DollarsEnd IfIf Len(pNumber) > 3 ThenpNumber = Left(pNumber, Len(pNumber) - 3)ElsepNumber = ""End IfxIndex = xIndex + 1LoopSelect Case DollarsCase ""Dollars = "No Dollars"Case "One"Dollars = "One Dollar"Case ElseDollars = Dollars & " Dollars"End SelectSelect Case CentsCase ""Cents = " and No Cents"Case "One"Cents = " and One Cent"Case ElseCents = " and " & Cents & " Cents"End SelectSpellNumberToEnglish = Dollars & CentsEnd FunctionFunction GetTens(pTens)Dim Result As StringResult = ""If Val(Left(pTens, 1)) = 1 ThenSelect Case Val(pTens)Case 10: Result = "Ten"Case 11: Result = "Eleven"Case 12: Result = "Twelve"Case 13: Result = "Thirteen"Case 14: Result = "Fourteen"Case 15: Result = "Fifteen"Case 16: Result = "Sixteen"Case 17: Result = "Seventeen"Case 18: Result = "Eighteen"Case 19: Result = "Nineteen"Case ElseEnd SelectElseSelect Case Val(Left(pTens, 1))Case 2: Result = "Twenty "Case 3: Result = "Thirty "Case 4: Result = "Forty "Case 5: Result = "Fifty "Case 6: Result = "Sixty "Case 7: Result = "Seventy "Case 8: Result = "Eighty "Case 9: Result = "Ninety "Case ElseEnd SelectResult = Result & GetDigit(Right(pTens, 1))End IfGetTens = ResultEnd FunctionFunction GetDigit(pDigit)Select Case Val(pDigit)Case 1: GetDigit = "One"Case 2: GetDigit = "Two"Case 3: GetDigit = "Three"Case 4: GetDigit = "Four"Case 5: GetDigit = "Five"Case 6: GetDigit = "Six"Case 7: GetDigit = "Seven"Case 8: GetDigit = "Eight"Case 9: GetDigit = "Nine"Case Else: GetDigit = ""End SelectEnd FunctionCopy

3。保存此代碼并返回工作表，在空白單元格中輸入此公式 = SpellNumberToEnglish（A2）( A2 是您要將數字轉換為英語貨幣單詞的單元格），然后將填充手柄向下拖動到要應用此公式的單元格，所有數字均已拼寫為英語貨幣單詞，請參見屏幕截圖：

使用有用的功能將數字轉換為貨幣單詞

上面的代碼對于Excel初學者來說有些麻煩，在這里，我將介紹一個有用的功能， Kutools for Excels 數字到單詞，使用此實用程序，您可以快速將數字拼寫為英文單詞或貨幣單詞。

提示：申請這個 數字到單詞 功能，首先，您應該下載 Kutools for Excel，然后快速輕松地應用該功能。

安裝后 Kutools for Excel，請這樣做：

1。選擇要轉換的數字列表，然后單擊 Kutools > 內容 > 數字到單詞，請參見屏幕截圖：

2。在 數字到貨幣單詞 對話框，檢查英語選項從語言列表框，然后單擊 Ok 按鈕，您將看到所有數字都已拼寫成英語貨幣單詞：

Tips：如果要獲取英語單詞，請檢查 不轉換為貨幣 復選框 附加選項 部分，請參見屏幕截圖：

點擊下載 Kutools for Excel 現在！