How to Convert Number to Words in Excel?

[vishrut-singhal]

Initially, Microsoft Excel was developed to process large data arrays. However, it also created accounting records like invoices, evaluations, or balance sheets quickly and effectively.

In more or fewer payment documents, it is necessary to duplicate numeric values with their word form. [Excel] doesn’t have a default function that displays numbers as English words in a worksheet. Still, you can add this capability by pasting the following SpellNumber function code into a VBA (Visual Basic for Applications) module. This function lets you convert dollar and cent amount to words with a formula.

For example, 25.50 would read as Twenty Five Dollars and Fifty Cents. This can be very useful if you’re using Excel as a template to print checks. So what you need is not just convert numbers to words in Excel (123.45 to one hundred and twenty three, forty five), but spell out dollars and cents ($29.95 to twenty nine dollars and ninety nine cents), pounds and pence for GBP, Euros, and Eurocents for EUR, etc.

If you want to convert numeric values to text format without displaying them as words, use the TEXT function as an alternative.

Even Excel 2016 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.

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. Usually, you need to change the cell format in Excel, do the following things:

• Select your range with numbers.
• Press Ctrl+1on the Number tab and select " Text " in the Category

How to Convert Numbers to Words Using SpellNumber VBA Macro

Microsoft didn’t want to add a tool for the number to words conversion. But they created and published the special VBA macro on their website.

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

The SpellNumber option can write dollars and cents. If you need a different currency, you can change “dollar” and “cent” with your choice. Below you will find a VBA code and follow the following steps, such as:

Step 1: Open the workbook where you need to spell the numbers into words.

Step 2: Press Alt + F11 to open the Visual Basic Editor window.

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, or one of the workbook elements is highlighted with blue.

Step 3: In the editor menu, go to the Insert tab and click on the Module button.

Step 4: You will see a window named Module1. Please select all of the code in the frame below and paste it to this window.

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 one's 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  

Step 5: Press Ctrl + S to save the updated worksheet.

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 ".

Step 6: A VB project dialog box appears. Click on the No button.

Step 7: You can save your file as an Excel Macro-Enabled Workbook (.xlsm) to keep your file in its current format.

• Click on the File tab.
• Select the Save As option.
• Click the Save as type drop-down menu.
• And in the save as type field, choose the " Excel macro-enabled workbook " file type.

Step 8: And click on the Save button to save the file.

Disadvantages of Using Macro to SpellNumber in Excel

• First, 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.