How to Convert Number to Word in Excel: Step-by-Step Guide for Beginners

Converting numbers to words in Excel might seem tricky, but it’s actually pretty simple once you know the steps. This guide will walk you through the process using a custom function, so you can easily turn numbers into words in any spreadsheet.

How to Convert Number to Word in Excel

This tutorial will guide you through creating a custom function in Excel to convert numbers into words. We will use Visual Basic for Applications (VBA) to write a script that performs this task.

Step 1: Open Excel and Access the Developer Tab

To get started, open Excel and enable the Developer tab.

The Developer tab is where you’ll find all the tools you’ll need to create and manage VBA scripts. If you don’t see it in your ribbon, go to Excel Options, select Customize Ribbon, and check the Developer option.

Step 2: Open the Visual Basic for Applications Editor

Once the Developer tab is visible, click on it and select "Visual Basic" to open the VBA editor.

The VBA editor is where you’ll write the code needed to create the custom function. Think of it as a separate workspace for coding within Excel.

Step 3: Insert a New Module

In the VBA editor, go to Insert > Module to create a new module.

A module is like a blank canvas for your VBA code. This is where you’ll write the script that converts numbers to words.

Step 4: Write the VBA Code

Copy and paste the following VBA code into the module:

Function NumberToWords(ByVal MyNumber)
    Dim Units As String
    Dim Teens As String
    Dim Tens As String
    Dim Hundreds As String
    Dim TempStr As String
    Dim DecimalPlace As Integer
    Dim Count As Integer
    Dim DecimalPart As String

    ' Strings for digit groups
    Units = "Zero One Two Three Four Five Six Seven Eight Nine"
    Teens = "Ten Eleven Twelve Thirteen Fourteen Fifteen Sixteen Seventeen Eighteen Nineteen"
    Tens = "Twenty Thirty Forty Fifty Sixty Seventy Eighty Ninety"
    Hundreds = " Hundred "

    MyNumber = Trim(CStr(MyNumber))
    DecimalPlace = InStr(MyNumber, ".")

    ' Convert the integer part
    Count = 1
    For i = Len(MyNumber) To 1 Step -1
        If Mid(MyNumber, i, 1)  "." Then
            TempStr = Mid(MyNumber, i, 1)
            Select Case Count
                Case 1 ' Units place
                    MyWords = Units(Val(TempStr) + 1) & MyWords
                Case 2 ' Tens place
                    If Val(TempStr) > 1 Then
                        MyWords = Tens(Val(TempStr) - 1) & " " & MyWords
                    ElseIf Val(TempStr) = 1 Then
                        MyWords = Teens(Val(Mid(MyNumber, i - 1, 2))) & " " & MyWords
                        Count = Count + 1
                    End If
                Case 3 ' Hundreds place
                    If Val(TempStr) > 0 Then
                        MyWords = Units(Val(TempStr) + 1) & Hundreds & MyWords
                    End If
            End Select
            Count = Count + 1
        End If
    Next i

    ' Convert the decimal part
    If DecimalPlace > 0 Then
        DecimalPart = Mid(MyNumber, DecimalPlace + 1)
        If Len(DecimalPart) > 0 Then
            MyWords = MyWords & " and " & DecimalPart & "/100"
        End If
    End If

    NumberToWords = MyWords
End Function

This code defines a function that takes a number and returns it as words. It handles units, tens, teens, and hundreds, and even includes the decimal part.

Step 5: Save and Close the VBA Editor

After pasting the code, save your work and close the VBA editor.

Saving your work ensures that your new function is ready to use. Closing the editor will take you back to your spreadsheet.

Step 6: Use the Function in Excel

Now, you can use the custom function just like any other Excel function. Type =NumberToWords(A1) in a cell.

Replace A1 with the cell that contains the number you want to convert. The function will display the number in words.

After following these steps, your number in Excel will be converted to words, making your data more readable and professional.

Tips for Converting Number to Word in Excel

• Double-check your spelling and grammar in the code to avoid errors.
• Make sure to save your workbook as a macro-enabled file (.xlsm).
• Test the function with different types of numbers to ensure it works correctly.
• Learn basic VBA to customize the function further.
• Use comments in your VBA code to make it easier to understand.

Frequently Asked Questions

What happens if my Developer tab is missing?

Go to Excel Options, select Customize Ribbon, and check the Developer option.

Is it possible to convert large numbers?

Yes, but you may need to adjust the VBA code to handle very large numbers.

Can I use this function in all versions of Excel?

This function works in Excel versions that support VBA, such as Excel 2010 and later.

What if I need to convert numbers in different worksheets?

You can use the function across different worksheets within the same workbook.

How can I ensure my function works correctly?

Test it with various numbers, including those with decimal points, to ensure accuracy.

Summary

1. Open Excel and access the Developer tab.
2. Open the Visual Basic for Applications editor.
3. Insert a new module.
4. Write the VBA code.
5. Save and close the VBA editor.
6. Use the function in Excel.

Conclusion

Converting numbers to words in Excel is a handy skill that can make your data look more professional and user-friendly. By following the steps outlined in this guide, you can easily set up a custom VBA function to do just that. If you’re new to VBA, don’t worry; this basic script is an excellent starting point for learning more about what VBA can do. Feel free to experiment and tweak the code to fit your specific needs.

For further reading, consider diving into more advanced VBA tutorials or exploring other custom functions to enhance your Excel capabilities. Remember, practice makes perfect, and before you know it, you’ll be an Excel wizard! Happy spreadsheeting!