如何在Excel中快速將數(shù)字轉換為英文單詞？
如果您在工作表中有一列數(shù)字值，現(xiàn)在需要將它們設置為等效的英語單詞或英語貨幣單詞，如下面的屏幕截圖所示。 在這里，我將討論如何快速，輕松地將數(shù)字更改為英語單詞。
使用用戶定義的功能將數(shù)字轉換為英文單詞
使用用戶定義的功能將數(shù)字轉換為貨幣詞
使用有用的功能將數(shù)字轉換為貨幣單詞
使用用戶定義的功能將數(shù)字轉換為英文單詞沒有任何功能或公式可以幫助您直接在Excel中解決此任務，但是您可以創(chuàng)建一個用戶定義函數(shù)來完成該任務。 請執(zhí)行以下步驟：
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 FunctionCopy3。 保存并關閉此代碼，然后返回工作表，在空白單元格中輸入此公式 = NumberstoWords（A2）( A2 是您要將數(shù)字轉換為英文單詞的單元格），請參見屏幕截圖：
4。 然后按 輸入 鍵，然后選擇單元格C2，然后將填充手柄拖動到要包含此公式的范圍。 所有數(shù)字值均已轉換為其相應的英語單詞。
備注：此代碼僅適用于整數(shù)，但不適用于十進制數(shù)字。
使用用戶定義的功能將數(shù)字轉換為貨幣詞如果要將數(shù)字轉換為英語貨幣單詞，則應應用下面的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 FunctionCopy3。 保存此代碼并返回工作表，在空白單元格中輸入此公式 = SpellNumberToEnglish（A2）( A2 是您要將數(shù)字轉換為英語貨幣單詞的單元格），然后將填充手柄向下拖動到要應用此公式的單元格，所有數(shù)字均已拼寫為英語貨幣單詞，請參見屏幕截圖：
使用有用的功能將數(shù)字轉換為貨幣單詞上面的代碼對于Excel初學者來說有些麻煩，在這里，我將介紹一個有用的功能， Kutools for Excels 數(shù)字到單詞，使用此實用程序，您可以快速將數(shù)字拼寫為英文單詞或貨幣單詞。
提示：申請這個 數(shù)字到單詞 功能，首先，您應該下載 Kutools for Excel，然后快速輕松地應用該功能。
安裝后 Kutools for Excel，請這樣做：
1。 選擇要轉換的數(shù)字列表，然后單擊 Kutools > 內容 > 數(shù)字到單詞，請參見屏幕截圖：
2。 在 數(shù)字到貨幣單詞 對話框，檢查 英語 選項從 語言 列表框，然后單擊 Ok 按鈕，您將看到所有數(shù)字都已拼寫成英語貨幣單詞：
Tips：如果要獲取英語單詞，請檢查 不轉換為貨幣 復選框 附加選項 部分，請參見屏幕截圖：
點擊下載 Kutools for Excel 現(xiàn)在！
最佳辦公生產力工具
將小時轉化為分鐘 Kutools for Excel!準備好增強您的 Excel 任務了嗎？ 利用的力量 Kutools for Excel - 您終極的節(jié)省時間的工具。 簡化復雜的任務并像專業(yè)人士一樣瀏覽數(shù)據(jù)。 以閃電般的速度體驗 Excel！
為什么需要 Kutools for Excel
