Spellnumber Function in Excel

In Excel, it’s common to work with numbers in various formats, but sometimes you might need to convert those numbers into written words—especially for financial documents or checks. Excel doesn’t have a built-in function for this purpose, but you can create your own Spellnumber function in Excel using VBA (Visual Basic for Applications). In this article, we will guide you through the step by step process of creating spellnumber function that converts the numbers to word or spelling format.

Here’s a step-by-step guide on how to do it.

Step 1: Open the VBA Editor

1. Open your Excel workbook.
2. Press ALT + F11 to open the VBA editor.

Step 2: Insert a New Module

1. In the VBA editor, go to Insert > Module. This will create a new module where you can write your VBA code.

Step 3: Write the SpellNumber Function

Copy and paste the following VBA code into the module:

Function Spellnumber(ByVal MyNumber)
    Dim Units As String
    Dim SubUnits As String
    Dim TempStr As String
    Dim DecimalPlace As Integer
    Dim Count As Integer
    Dim Hundred As String
    
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "

    ' Convert MyNumber to string and trim white space.
    MyNumber = Trim(CStr(MyNumber))

    ' Find position of decimal place.
    DecimalPlace = InStr(MyNumber, ".")
    
    ' Convert SubUnits and set MyNumber to Units amount.
    If DecimalPlace > 0 Then
        SubUnits = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If

    Count = 1
    Do While MyNumber <> ""
        TempStr = GetHundreds(Right(MyNumber, 3))
        If TempStr <> "" Then Units = TempStr & Place(Count) & Units
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop

    Spellnumber = Application.Trim(Units)
End Function

Private 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

Private 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

Private 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 4: Save and close the VBA Editor

1. Click File > Save in the VBA editor.
2. Close the VBA editor by clicking the X button or pressing ALT + Q.

Step 5: Use the SpellNumber Function in Excel

1. Go back to your Excel workbook.
2. Enter a number in any cell.
3. In another cell, type =SpellNumber(A1) (replace A1 with the cell containing your number).

Step 6: Save The Workbook as Macro-Enabled File Type

To save this Spellnumber function in your workbook and use it next time you open the file, you have to save the file as macro-enabled type. Follow these steps to do it.

1. Click on File Tab.
2. Click “Save As”.
3. In the file type, choose “Excel Macro-Enabled Workbook (*.xlsm)
4. Choose save location and click “Save”.

Conclusion

Creating a SpellNumber function in Excel using VBA can save you time and effort, especially when dealing with financial documents or any situation where numeric values need to be expressed in words. With just a bit of coding, you can extend Excel’s capabilities and make your data more versatile.