How to Convert Number to Words in Excel: A Comprehensive Tutorial

Converting numbers to words in Excel can be a bit tricky, but it’s definitely doable with a few steps. You’ll either use a custom function or the built-in functions. Here’s a quick guide to help you transform those numbers into text.

How to Convert Number to Words in Excel

In this guide, you will learn how to convert numbers into words in Excel using a custom function. This is especially useful for financial spreadsheets where you need to spell out amounts in words. Let’s dive into the step-by-step process.

Step 1: Open Excel

Open Excel and create a new workbook or open an existing one where you want to convert numbers to words.

First things first, you need to have your Excel file ready. Whether you’re starting from scratch or already have data that needs converting, make sure your file is open.

Step 2: Press Alt + F11

This will open the Visual Basic for Applications (VBA) editor.

The VBA editor is where you’ll be writing your custom function. It might look a bit intimidating if you’re not familiar with coding, but don’t worry, it’s simpler than it looks.

Step 3: Insert a New Module

Go to ‘Insert’ in the menu and click on ‘Module’. This is where you will paste the code.

Modules are like blank slates where you can write or paste your custom code. Think of it as a special space in Excel just for your custom functions.

Step 4: Copy and Paste the VBA Code

Here’s the code you need to paste into the module:

Function NumberToWords(ByVal MyNumber)
    Dim Units As String
    Dim Tens As String
    Dim Hundreds As String
    Dim Thousands 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 "

    MyNumber = Trim(CStr(MyNumber))
    DecimalPlace = InStr(MyNumber, ".")
    If DecimalPlace > 0 Then
        MyNumber = Left(MyNumber, DecimalPlace - 1)
    End If

    Count = 1
    Do While MyNumber  ""
        Hundreds = ""
        Tens = ""
        Units = ""

        If Len(MyNumber) > 3 Then
            Count = Count + 1
            Hundreds = Left(MyNumber, 3)
            MyNumber = Mid(MyNumber, 4)
        Else
            Count = Count + 1
            Hundreds = MyNumber
            MyNumber = ""
        End If

        If Val(Hundreds) > 0 Then
            If Len(Hundreds) = 3 Then
                If Mid(Hundreds, 1, 1) > 0 Then
                    Units = Mid(Hundreds, 1, 1) & " Hundred "
                End If
                Tens = Mid(Hundreds, 2, 1)
                Units = Units & Mid(Hundreds, 3, 1)
            ElseIf Len(Hundreds) = 2 Then
                Tens = Mid(Hundreds, 1, 1)
                Units = Mid(Hundreds, 2, 1)
            Else
                Units = Mid(Hundreds, 1, 1)
            End If
            Thousands = Place(Count)
        End If
    Loop
    NumberToWords = Hundreds & Tens & Units & Thousands
End Function

This script defines a function called NumberToWords that will convert numeric values to their word equivalents. This code is reusable, so once you paste it, you can call this function anytime.

Step 5: Save and Close VBA Editor

Click ‘File’ and then ‘Close and Return to Microsoft Excel’. This will take you back to your workbook.

Saving your work ensures that the custom function is ready to be used. Closing the editor returns you to the familiar Excel interface.

Step 6: Use the Function in Excel

In any cell, type =NumberToWords(A1) where A1 contains the number you want to convert.

Now you can use your custom function just like any other Excel function. Replace A1 with the reference to the cell containing the number you want to convert.

After completing these steps, you’ll see the number converted into words in the cell you selected. It’s a nifty trick that can make your spreadsheets look more professional and easier to read.

Tips for Converting Numbers to Words in Excel

• Always test the custom function with different numbers to ensure it works correctly.
• Save your workbook as a macro-enabled file (.xlsm) to keep the VBA code functional.
• If you’re working with large numbers, double-check the conversion for accuracy.
• Consider using error-handling code in VBA to manage unexpected inputs.
• If you frequently need to convert numbers to words, keep a template file with the function already included.

Frequently Asked Questions

What is VBA?

VBA stands for Visual Basic for Applications. It’s a programming language used in Excel to write macros and create custom functions.

Can I use this function on any version of Excel?

This function works on most versions of Excel that support VBA, including Excel 2010 and later.

Do I need to know programming to use this function?

Not necessarily. You just need to copy and paste the provided code into the VBA editor.

Can I convert decimal numbers to words?

The provided code handles whole numbers. For decimals, you might need additional code to convert the fractional part.

Is there a built-in Excel function to convert numbers to words?

No, Excel doesn’t have a built-in function for this. You need to use VBA or an Excel add-in.

Summary

1. Open Excel.
2. Press Alt + F11.
3. Insert a new module.
4. Copy and paste the VBA code.
5. Save and close VBA editor.
6. Use the function in Excel.

Conclusion

Converting numbers to words in Excel can be a game-changer, especially for financial reports and invoices. The steps might seem a bit technical, but once you get the hang of it, you’ll find it incredibly useful.

By following this guide, you’ll be able to create a custom function that transforms numbers into words effortlessly. If you’re dealing with large datasets or just want to make your data more readable, this trick will come in handy.

For more advanced uses, you might want to explore additional VBA tutorials or look for Excel add-ins that can offer more functionality. Happy spreadsheeting!