Download

DescriptionSizeDownload

The template works with both the Microsoft Store edition and the desktop edition of the app. Make sure you have installed one of them.

How to use:

  1. Download the EXE package.
  2. Double-click to extract the files into a folder. Make sure you have full access privileges to the folder, such as a subfolder inside “My Documents”.
  3. Open the template.
499 KBDownload

License: Private Use (not for distribution or resale). See our Terms of Use.

Screenshot

screenshot for Advanced Sample: Invoiced Total in English Words

Detail

This form demonstrates how to display the total in English words.

Like other Office (Microsoft 365) programs, Microsoft Excel supports VBA (Visual Basic for Applications) macros, which you can use to extend the functionalities.

While Excel itself does not have a formula to convert a numeric value into English words, it is possible to create a VBA function to do this.

To save the macro function code with a template, the template must be converted into ".xlsm" format. See Macro-Enabled Template (c5101) for more information.

This tutorial is based on Oval Button (c5099). We'll add the macro function "SpellNumber" to the template.

First, convert the ".xlsx" file to ".xlsm" format, as detailed below:

  1. Open the template (workbook file).
  2. Click the Excel menu "File", and then "Save As".
  3. Choose the location (folder) to save the new workbook file.
  4. On the "Save As" dialog box, from the "Save as type" drop-down list, choose "Excel macro-enabled workbook (*.xlsm)", and then type in the file name for the new workbook.
  5. Click "Save".

Now for the steps to create the "SpellNumber" VBA macro function. The macro code was copied from Microsoft.com at Convert numbers into words.

  1. Press ALT+F11 on your keyboard. This opens the "Microsoft Visual Basic for Applications" editor.
  2. Click the menu "Insert", and then "Module". Thins inserts a new module into the current VBA project.
  3. Copy and paste the following VBA code into the top-right pane.

    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 "
        ' String representation of amount.
        MyNumber = Trim(Str(MyNumber))
        ' Position of decimal place 0 if none.
        DecimalPlace = InStr(MyNumber, ".")
        ' Convert cents and set MyNumber to dollar amount.
        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
          
    ' Converts a number from 100-999 into text 
    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
          
    ' Converts a number from 10 to 99 into text. 
    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
         
    ' Converts a number from 1 to 9 into text. 
    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
    

  4. Click the "Save" button on the toolbar.
  5. Switch to design mode by clicking the "Design Mode" button on the ribbon.
  6. Click to select the Excel cell where you want to show the English words. In this example, we choose $K$45 on the worksheet.
  7. Enter the formula to convert the amount into English words:

    =SpellNumber(oknBalanceDue)

    If your form does not show the balance and you want to show the "Total" instead, use this formula:

    =SpellNumber(oknTotal)

    For example, if the amount to convert is "756.00", the result will be "Seven Hundred Fifty Six Dollars and No Cents".

  8. Exit design mode.
  9. Save the template.

Format and Specification

Template#c5102
Belongs to

Sales

Format (XLS or XLSX).xlsm
Columns5
Lines12
Line Height (Points)18.00
Print Area$D$3:$K$46
Papaer OrientationPortrait
Default Margins (Points)
Left18.00
Right18.00
Top75.60
Bottom75.60
PriceFree