How to spell out or convert numbers to English words in Excel?
Supposing you have a lot of price amount numbers, and now you need to convert or spell out them to text strings. For example, convert 123.55 to the English words one hundred twenty-three dollars and fifty five cents. In Excel there are no effective facility to solve this problem but using the long and complex VBA code. In this article, I will introduce you some methods to deal with it.
Recommended Productivity Tools
The following long VBA code can help you to spell out numbers to text strings. Do as follows:
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 macro in the Modulewindow.
Function SpellNumberToEnglish(ByVal pNumber) 'Updateby20131113 Dim Dollars, Cents arr = Array("", "", " Thousand ", " Million ", " Billion ", " Trillion ") pNumber = Trim(Str(pNumber)) xDecimal = InStr(pNumber, ".") If xDecimal > 0 Then Cents = GetTens(Left(Mid(pNumber, xDecimal + 1) & "00", 2)) 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 Select Case Dollars Case "" Dollars = "No Dollars" Case "One" Dollars = "One Dollar" Case Else Dollars = Dollars & " Dollars" End Select Select Case Cents Case "" Cents = " and No Cents" Case "One" Cents = " and One Cent" Case Else Cents = " and " & Cents & " Cents" End Select SpellNumberToEnglish = Dollars & Cents 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. Then save the code, and return to the worksheet, in the adjacent cell E1, enter the formula =SpellNumberToEnglish(D1), see screenshot:
4. Then press Enter key, and select the cell E1, then drag the fill handle over to the range that contains this formula as you need. And all of the numbers have been spelt out to English words. See screenshot:
Note: As they are formulas, when you need to copy and paste them, please paste as values.
Maybe the above long code can make you be mad, please don't worry, here I can give you an easy and quick way to solve it.
With the Kutools for Excel’s Spell Out Numbers function, you can quickly and conveniently convert the numbers to text strings.
|Kutools for Excel, with more than 120 handy functions, makes your jobs easier.|
After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)
1. Select the range numbers that you want to spell out.
2. Click Kutools > Content > Spell Out Numbers. See screenshot:
3. In the Spell Out Numbers dialog box, choose English from the Languages. And you can preview the results from the right Preview Pane. See screenshot:
4. Then click Ok or Apply. And all of the price amount numbers have been converted to text strings in the original range.
Note: If you need the Chinese price amount words, please click Chinese option from the Languages.
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
200 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets 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.· 1 months agoi wan to change this eg:( one hundred thousand rupees only ), pls explain me how to get that word "only"
To post as a guest, your comment is unpublished.· 2 months agoI just want to write like this (USD One Thousand One Hundred Ten and Cents Forty Five Only.) but how to convert like this?
To post as a guest, your comment is unpublished.· 2 months agoHow can I convert so that the word "and" appears before the last two or three digits are converted.. For example.. at the moment the script converts 131 as One Hundred Thirty One.. but I want to convert as.. One Hundred AND Thirty one.. so 77762 should appear as.. Seventy Seven Thousand Seven Hundred AND Sixty Two.. is that possible? Thanks!
To post as a guest, your comment is unpublished.· 1 months agoSonny, did you get a solution to your question? Please share with me cause am facing same problem. I want 131 to be converted as One Hundred and Thirty One but not One Hundred Thirty One. Any Help?
To post as a guest, your comment is unpublished.· 2 months agoYes Sonny that's is correct way to spell 77762
To post as a guest, your comment is unpublished.· 3 months agoHow can i get actuall figure without "no cents/ no dollars", if it's not needed. Please Email me in shown Mail ID.
To post as a guest, your comment is unpublished.· 3 months ago[quote name="jabesh"]Please change all the words ~dollar/dollars" and ~cent/cents" in the code into your currency name.[/quote]
change peso to dollar and change cent into 5/100
- ← Previous
- Next →