Aug
29

Two best ways to convert numbers to words in Excel

In this article I will show you two quick and free ways to convert currency numbers into English words in Excel 2010, 2013 and other versions.

Microsoft Excel is a great program to calculate this and that. It was initially developed to process large data arrays. However, it also lets creating accounting records like invoices, evaluation or balance sheets quickly and effectively.

In more or less solid payment documents it is necessary to duplicate numeric values with their word form. It is much harder to falsify typed numbers than those written by hand. Some swindler can try to make 8000 out of 3000, while it is almost impossible to secretly replace "three" with "eight".

So what you need is not just convert numbers to words in Excel (е.g. 123.45 to "one hundred and twenty three, forty five"), but spell out dollars and cents (е.g. $29.95 as "twenty nine dollars and ninety nine cents" ), pounds and pence for GBP, euros and eurocents for EUR, etc.

Convert number to words in Excel 2010

Even Excel 2013 doesn't have a built-in tool for spelling numbers, not to mention earlier versions. But that is when Excel is really good. You can always improve its functionality using formulas in all their
combinations, VBA macros, or third-party add-ins.

Below you'll find two ways to convert numbers from figures to words

And, possibly, you may need to convert Words to Numbers in Excel

Note

If you are looking for the number to text conversion, which means you want Excel to see your number as text, it's a bit different thing. Usually, you simply need to change the cell format in Excel (select your range with numbers, press Ctrl+1 on the Number tab, and select "Text" in the Category field). You can also use the formula =text().
Please find the details in the article Ways to convert numbers to text in Excel.

SpellNumber VBA macro to convert numbers to words

As I have already mentioned, Microsoft didn't want to add a tool for this task. However, when they saw how many users needed it, they created and published the special VBA macro on their website. The macro does what its name SpellNumber suggests. All other macros I came across are based on the Microsoft code.

You can find the macro mentioned as "spellnumber formula". However, it is not a formula, but a macro function, or to be more precise Excel User defined function (UDF).

The spellnumber option is able to write dollars and cents. If you need a different currency, you can change "dollar" and "cent" with the name of your one.

If you are not a VBA savvy guy, below you will find a copy of the code. If you still don't want or haven't time to sort this out, please use this solution.

1. Open the workbook where you need to spell the numbers.

2. Press ALT+F11 to open the Visual Basic editor window.

3. If you have several books opened, check that the needed workbook is active using the list of projects in the upper left corner of the editor (one of the workbook elements is highlighted with blue).

4. In the editor menu go to Insert-> Module.
Excel Visual Basic editor - insert module

5. You should see a window named YourBook - Module1. Select all of the code in the frame below and paste it to this window.
SpellNumber VBA macro to convert numbers to words

Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
    Dim Dollars, Cents, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "

    MyNumber = Trim(Str(MyNumber))
    DecimalPlace = InStr(MyNumber, ".")
    If DecimalPlace > 0 Then
        Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
                  "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber <> ""
        Temp = GetHundreds(Right(MyNumber, 3))
        If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 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
    SpellNumber = Dollars & Cents
End Function

Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
    ' Convert the hundreds place.
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
    ' Convert the tens and ones place.
    If Mid(MyNumber, 2, 1) <> "0" Then
        Result = Result & GetTens(Mid(MyNumber, 2))
    Else
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
End Function

Function GetTens(TensText)
    Dim Result As String
    Result = "" ' Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
        Select Case Val(TensText)
            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 ' If value between 20-99...
        Select Case Val(Left(TensText, 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(TensText, 1))  ' Retrieve ones place.
    End If
    GetTens = Result
End Function

Function GetDigit(Digit)
    Select Case Val(Digit)
        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

6. Press Ctrl+S to save the updated workbook.

You will need to resave your workbook. When you try to save the workbook with a macro you'll get the message "The following features cannot be saved in macro-free workbook"

The following features cannot be saved in macro-free Workbook : VB project

Click No. When you see a new dialog, chose the Save as option. In the field "Save as type" pick the option "Excel macro-enabled workbook".

Save your book as 'Excel macro-enabled workbook'

Use SpellNumber macro in your worksheets

Now you can use the function SpellNumber in your Excel documents. Enter =SpellNumber(A2) into the cell where you need to get the number written in words. Here A2 is the address of the cell with the number or amount.

Use SpellNumber macro in your Excel worksheets

Here you can see the result:
Spelled out numbers to dollars and cents

Voila!

Quickly copy the SpellNumber function to other cells.

If you need to convert the entire table, not just 1 cell, place your mouse cursor to the lower right corner of the cell with the formula until it turns into a small black cross:

Quickly copy the SpellNumber function to other cells

Left-click and drag it across the column to fill in the formula. Release the button to see the results:

Spelled out numbers to dollars and cents

Note

Please keep in mind that if you use SpellNumber with a link to another cell, the written sum will be updated each time the number in the source cell is changed.
You can also enter the number directly into the function, for example, =SpellNumber(29.95) (29.95 - without quotation marks and the Dollar sign).

Disadvantages of using macro to spell numbers in Excel

First off, you must know VBA to modify the code according to your needs. It is necessary to paste the code for each workbook, where you plan to change it. Otherwise, you will need to create a template file with macros and configure Excel to load this file at each start.

The main disadvantage of using a macro is if you send the workbook to somebody else, this person will not see the text unless the macro is built into the workbook. And even if it's built-in, they will get an alert that there are macros in the workbook.

Add a macro to your workbook

Free ready-to-use add-in to spell numbers into words

For active Excel users who need to quickly spell sums but don't want to learn VBA or other workarounds, one kind developer created a free add-in - POPUP SPELL NUMBER for Microsoft Excel

Besides being ready for use, the tool is really flexible in converting numbers. You can choose the text case of the result (lower case, Title Case, Sentence case, UPPER CASE ), also you can select how you want to see the fraction:

  • Spells the fraction in cent/cents, оr penny/pennies, оr penny/pence, or centavo/centavos
  • Spells decimal point, each zero and the rest of the number as an integer.
  • Doesn't spell the fraction but writes it as a fraction with denominator of 100, 1000, 1000000

Feel free to explore its possibilities on the product's home page.

The author states that it's tested and works in Excel for Windows 2000, 2002(XP), 2003, 2007, 2010 (32-bit), and 2013 (32-bit) in MSI-based and Click-To-Run installations of Office 365 cloud-based services.

And all is wonderful about POPUP SPELL NUMBER, but...

I hate to say that the add-in doesn't work with 64-bit versions of Excel 2010, 2013, 2007. And it is a considerable obstacle since these versions are becoming more and more popular. So lucky you are if you have Excel 32-bit :) Feel free to download the add-in and check it out.

If you are the one with Excel 64-bit like me, drop the author a line and ask him for a special version.

Reverse conversion - English words into numbers

Frankly, I can't imagine why you may need it. Just in case... :)

It appears that Excel MVP, Jerry Latham, created such Excel User defined function (UDF) as WordsToDigits. It converts English words back to number.

You can download Jerry's WordsToDigits workbook to see the UDF code. Here you'll also find his examples of how to use the function.

Convert english words into numbers

You can see how the function works on the sheet "Sample Entries", where you will also be able to enter your own examples. If you plan to employ WordsToDigits in your documents, please be informed that this function has restrictions. For example, it doesn't recognize fractions entered in words. You will find all the details on the "Information" sheet.

See also

15 Responses to "Two best ways to convert numbers to words in Excel"

  1. Rahim Zulfiqar Ali says:

    You can also do it by Formula:

    =TRIM(IF(A2=10^5,CHOOSE(FLOOR(MOD(A2,10^6)/10^5,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")&" Hundred","")&IF(MOD(A2,10^5)>=20000," "&CHOOSE(FLOOR(MOD(A2,10^5)/10^4,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety")&IF(MOD(MOD(A2,10^5),10^4)>=10^3," "&CHOOSE(FLOOR(MOD(MOD(A2,10^5),10^4)/10^3,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"),""),IF(MOD(A2,10^5)>=10^3," "&CHOOSE(FLOOR(MOD(A2,10^5)/10^3,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"),""))&" Thousand") & IF(A2=10^2," "&CHOOSE(FLOOR(MOD(A2,10^3)/10^2,1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen")&" Hundred","")&IF(MOD(A2,10^2)>=20," "&CHOOSE(FLOOR(MOD(A2,10^2)/10,1)+1,"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"
    )&IF(MOD(MOD(A2,10^2),10)>=1," "&CHOOSE(FLOOR(MOD(MOD(A2,10^2),10),1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"),""),IF(MOD(A2,10^2)>=1," "&CHOOSE(FLOOR(MOD(A2,10^2),1)+1,"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"),""))&""))

    • admin says:

      I'm really sorry, when you posted the formula, an error occurred. So and pasting it to Excel directly doesn't work. Please send us the working copy in an Excel file. I will be happy to add your formula to the article and sign it with your name.

    • Tristan says:

      How would you trim this formula down if you're only using whole numbers between 1 and 12?

  2. Jennifer says:

    Hi! Thanks for the formula. It works, I have created the VB formula. But what should I do if I only want the words "Six Only" rather than "Six Dollars and None Cents?"

    My currency happens to be different so I prefer without the currency in words. Thanks!

  3. Parvendar Rathi says:

    There is some Error in this formula becouse the every time after close Excel file then we open next time file we have need to microsoft visual basic for application and save the same seting again and agian. we want to that after one time save the setting the formula contuning in work.

  4. Raz says:

    hi sir
    please help me to do spellnumber code in check format

    Ex. if i type $1,000.15
    it will be ***One Thousand Dollar and 15/100 Only***

    im so glad if you help me

    thank you :D

  5. Anonymous says:

    Option Explicit
    'Main Function
    Function SpellNumber(ByVal MyNumber)
    Dim Riyals, Halalas, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "

    MyNumber = Trim(Str(MyNumber))
    DecimalPlace = InStr(MyNumber, ".")
    If DecimalPlace > 0 Then
    Halalas = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
    "00", 2))
    MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber ""
    Temp = GetHundreds(Right(MyNumber, 3))
    If Temp "" Then Riyals = Temp & Place(Count) & Riyals
    If Len(MyNumber) > 3 Then
    MyNumber = Left(MyNumber, Len(MyNumber) - 3)
    Else
    MyNumber = ""
    End If
    Count = Count + 1
    Loop
    Select Case Riyals
    Case ""
    Riyals = "No Riyals"
    Case "One"
    Riyals = "One Riyal"
    Case Else
    Riyals = Riyals & " Riyals"
    End Select
    Select Case Halalas
    Case ""
    Halalas = " and 0 Halalas"
    Case "One"
    Halalas = " and One Cent"
    Case Else
    Halalas = " and " & Halalas & " Halalas"
    End Select
    SpellNumber = Riyals & Halalas
    End Function

    Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
    ' Convert the hundreds place.
    If Mid(MyNumber, 1, 1) "0" Then
    Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
    ' Convert the tens and ones place.
    If Mid(MyNumber, 2, 1) "0" Then
    Result = Result & GetTens(Mid(MyNumber, 2))
    Else
    Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
    End Function

    Function GetTens(TensText)
    Dim Result As String
    Result = "" ' Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
    Select Case Val(TensText)
    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 ' If value between 20-99...
    Select Case Val(Left(TensText, 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(TensText, 1)) ' Retrieve ones place.
    End If
    GetTens = Result
    End Function

    Function GetDigit(Digit)
    Select Case Val(Digit)
    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

  6. Ali Ahmed says:

    very helpful thanks indeed

  7. azeem says:

    can you please provide me the easiest way to find spellnumber to convert numbers to words

  8. hui says:

    if my number is $500.07 and my word want to convert to "Say Dollars Five Hundreds and cents Seven Only). What is the format for this?

  9. hazem says:

    your solution is amazing but i wonder if i just want the number as it is without dollars without saudi rayal without any currency name , how can i do that in the module

Post a comment



Please note that we are on holiday until Sep-16, so we won’t be able to reply to your comments for a while, sorry.
Ultimate Suite for Excel Professionals
 
 
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard