How to Spell Number in Excel: A Step-by-Step Guide for Beginners

Spelling a number in Excel means converting numerical digits into their text equivalent. This task can be achieved by using custom functions and formulas. It involves using Excel’s built-in features and possibly adding a bit of VBA (Visual Basic for Applications) code. Let’s dive into how you can do this step-by-step.

How to Spell Number in Excel

This section will take you through the steps to convert numbers into text format in Excel. By following these instructions, you’ll be able to write a number like "123" as "One Hundred Twenty-Three" in your worksheet.

Step 1: Open Excel

First things first, you need to open Microsoft Excel on your computer.

Make sure you have the right worksheet open where you want to perform this operation.

Step 2: Press ALT + F11

To access the VBA editor, press ALT and F11 simultaneously.

The VBA editor is where you’ll write the code necessary to perform the number-to-text conversion.

Step 3: Insert a New Module

Next, go to the Insert menu and select "Module."

This action will create a blank module where you can type or paste your VBA code.

Step 4: Enter the VBA Code

Copy and paste the following VBA code into the module:

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

    ' Convert MyNumber to string and find decimal place.
    MyNumber = Trim(CStr(MyNumber))
    DecimalPlace = InStr(MyNumber, ".")

    ' Convert cents and set MyNumber to dollar amount.
    If DecimalPlace > 0 Then
        TempStr = 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)) & Place(Count) & TempStr
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop

    SpellNumber = Application.Trim(TempStr)
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

This code creates a function called SpellNumber that converts numerical values into their text equivalents.

Step 5: Save and Close VBA Editor

Once you’ve entered the code, save your work and close the VBA editor by clicking the X button in the top right corner.

Now, you’re back in your Excel worksheet.

Step 6: Use the SpellNumber Function

In any cell, type =SpellNumber(A1) where A1 is the cell containing the number you want to spell out.

When pressed Enter, the number in cell A1 will be converted into text in your selected cell.

After completing these steps, your numbers will be spelled out in words in Excel. This can be incredibly useful for reports, invoices, or any document requiring numbers in text form.

Tips for Spelling Number in Excel

1. Make sure to save your work frequently when using VBA, as errors can sometimes cause Excel to crash.
2. Customize the VBA code for different languages if you need to spell numbers in a language other than English.
3. Use named ranges to make your formulas easier to read and manage.
4. Test the function with a variety of numbers to ensure it works correctly, including large numbers and decimal points.
5. Consider creating a macro-enabled template if you frequently need this functionality.

Frequently Asked Questions

What is VBA in Excel?

VBA stands for Visual Basic for Applications, a programming language used to automate tasks in Excel.

Do I need to know how to code to use VBA?

Not necessarily. You can copy and paste code, but understanding the basics will help you make adjustments.

Can I use this function in Google Sheets?

No, VBA is specific to Excel. Google Sheets uses a different scripting language called Google Apps Script.

Will this work on Mac versions of Excel?

Yes, but ensure you have the VBA editor enabled, as some versions may require additional setup.

How can I troubleshoot if the function doesn’t work?

Check for syntax errors in your VBA code and ensure you typed the function name correctly in Excel.

Summary

1. Open Excel.
2. Press ALT + F11.
3. Insert a New Module.
4. Enter the VBA Code.
5. Save and Close VBA Editor.
6. Use the SpellNumber Function.

Conclusion

Spelling number in Excel is a task that can be easily accomplished with a bit of VBA magic. While it may seem daunting at first, breaking down the steps makes it much more manageable. This function can save you time and effort, especially when dealing with large sets of data that require number-to-text conversion.

Remember to test the function thoroughly and make any necessary adjustments to the code based on your specific needs. Whether you’re preparing financial reports, creating invoices, or simply trying to add a bit of polish to your data, converting numbers to text in Excel is a handy skill to have.

So, roll up your sleeves, dive into the world of VBA, and transform those numbers into words with ease! Happy Excel-ing!