How to quickly convert / change numbers to words in Excel?
If you have a column of numeric values in a worksheet, and now you need to convent them to their equivalent English words (as following screenshot shown). Here I will talk about a method to change the numbers to English words quickly and easily.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
There are no features or formulas can help you to solve this task directly in Excel, but you can create a User Defined Function to finish it. Please do with following steps:
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications Window.
2. Click Insert > Module, and paste the following code in the Module Window.
Function NumberstoWords(ByVal pNumber) 'Updateby20140220 Dim Dollars arr = Array("", "", " Thousand ", " Million ", " Billion ", " Trillion ") pNumber = Trim(Str(pNumber)) xDecimal = InStr(pNumber, ".") If xDecimal > 0 Then pNumber = Trim(Left(pNumber, xDecimal - 1)) End If xIndex = 1 Do While pNumber <> "" xHundred = "" xValue = Right(pNumber, 3) If Val(xValue) <> 0 Then xValue = Right("000" & xValue, 3) If Mid(xValue, 1, 1) <> "0" Then xHundred = GetDigit(Mid(xValue, 1, 1)) & " Hundred " End If If Mid(xValue, 2, 1) <> "0" Then xHundred = xHundred & GetTens(Mid(xValue, 2)) Else xHundred = xHundred & GetDigit(Mid(xValue, 3)) End If End If If xHundred <> "" Then Dollars = xHundred & arr(xIndex) & Dollars End If If Len(pNumber) > 3 Then pNumber = Left(pNumber, Len(pNumber) - 3) Else pNumber = "" End If xIndex = xIndex + 1 Loop NumberstoWords = Dollars End Function Function GetTens(pTens) Dim Result As String Result = "" If Val(Left(pTens, 1)) = 1 Then Select 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 Else End Select Else Select 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 Else End Select Result = Result & GetDigit(Right(pTens, 1)) End If GetTens = Result End Function Function 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 Select End Function
3. Save and close this code and go back the worksheet, in a blank cell, enter this formula =NumberstoWords(A2)( A2 is the cell you want to convert the number to English word), see screenshot:
4. Then press Enter key, and select cell C2 then drag the fill handle to the range that you want to contain this formula. All the numeric values have been converted their corresponding English words.
Note: When you need to copy these results to other cells, please copy and paste them as Values.
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 months agohow can i get 0 = - or zero
To post as a guest, your comment is unpublished.· 4 months agoI have an own set of formula combinations to convert numbers from 0 (zero) to 9999999 (Ninety Nine Lakhs Ninety Nine Thousand Nine Hundred Ninety Nine).
(This number is the upper limit for my method. It will return an error for any number out of this range). Secondly, as it is Excel formula based method, only one cell can be converted at a time.
Send your request to shreepadg at the rate gmail dot com
To post as a guest, your comment is unpublished.· 5 months agoThank you extendOfficea
To post as a guest, your comment is unpublished.
To post as a guest, your comment is unpublished.· 7 months agoThanks
Its working! :)