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

by

How to Convert Digit to Word in Excel

Need to convert digits to words in Excel? No problem! Here’s a brief overview of how to accomplish this task. You’ll use a combination of a custom function and Excel formulas to turn numerical values into their corresponding word forms. Whether it’s for financial reports or just to impress your boss, this guide has you covered.

Step-by-Step Tutorial on How to Convert Digit to Word in Excel

In this section, we’ll walk you through the necessary steps to convert digits into words in Excel. By the end of this tutorial, you will have all the tools you need to transform any numerical value into text form.

Step 1: Open Excel and Press Alt + F11

Step 1: Open Excel and press Alt + F11 to access the Visual Basic for Applications (VBA) editor.

Opening the VBA editor is your first step toward creating a custom function. This window is where you’ll write the code to convert digits to words.

Step 2: Insert a New Module

Step 2: In the VBA editor, click on Insert > Module to create a new module.

By inserting a new module, you are creating a space to input your custom code. This module will house the VBA script needed for the conversion.

Step 3: Copy and Paste the VBA Code

Step 3: Copy and paste the following VBA code into the module:

Function NumToWords(ByVal MyNumber)
    Dim Units As String
    Dim Tens As String
    Dim Hundreds As String
    Dim Thousands As String
    Dim Temp As String
    Dim DecimalPlace As Integer
    Dim Count As Integer
    Dim DecimalPart As String

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

' Find position of decimal place
    DecimalPlace = InStr(MyNumber, ".")

' Convert decimal part
    If DecimalPlace > 0 Then
        DecimalPart = Mid(MyNumber, DecimalPlace + 1)
        MyNumber = Left(MyNumber, DecimalPlace - 1)
    End If

' Convert integer part
    Count = 1
    Do While MyNumber  ""
        Select Case Count
            Case 1
                Temp = ConvertHundreds(Right(MyNumber, 3)) & Temp
            Case 2
                Temp = ConvertHundreds(Right(MyNumber, 3)) & " Thousand " & Temp
            Case 3
                Temp = ConvertHundreds(Right(MyNumber, 3)) & " Million " & Temp
            Case 4
                Temp = ConvertHundreds(Right(MyNumber, 3)) & " Billion " & Temp
        End Select
        MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Count = Count + 1
    Loop

    NumToWords = Application.Trim(Temp)

' Add decimal part
    If DecimalPart  "" Then
        NumToWords = NumToWords & " and " & DecimalPart & "/100"
    End If
End Function

Function ConvertHundreds(ByVal MyNumber)
    Dim Result As String
    Result = ""
    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

    ConvertHundreds = Result
End Function

Function GetTens(TensText)
    Dim Result As String
    Result = ""
    If Val(Left(TensText, 1)) = 1 Then
        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
        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))
    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 VBA code defines a function that converts numerical values into their word equivalent. Copying and pasting this code correctly is crucial for the function to work.

Step 4: Save and Close the VBA Editor

Step 4: Save your work by clicking on the disk icon, then close the VBA editor.

Saving your work ensures that you don’t lose the custom function you’ve just created. Closing the editor will bring you back to the Excel workbook.

Step 5: Use the Custom Function in Excel

Step 5: In any cell, type =NumToWords(A1) where A1 is the cell containing the number you want to convert.

Typing this formula calls the custom function, converting the number in cell A1 to words. You can use this formula in any cell by referring to the appropriate cell containing a number.

After you’ve completed these steps, the number in the chosen cell will be converted to words. It’s that simple!

Tips on Converting Digit to Word in Excel

  • Make sure your Excel macros are enabled, or the custom function won’t work.
  • Use descriptive names for your VBA functions to avoid confusion.
  • Test the function with different numbers to ensure it works properly.
  • Save your workbook as a macro-enabled file (.xlsm) to retain the custom function.
  • If you encounter errors, double-check the VBA code for any typos or missing elements.

Frequently Asked Questions on Converting Digit to Word in Excel

Can I use this method in all versions of Excel?

Yes, this method works in all versions of Excel that support VBA, including Excel 2007, 2010, 2013, 2016, 2019, and Office 365.

Do I need to enable macros to use this function?

Yes, enabling macros is necessary for the custom VBA function to work.

Can this function handle large numbers?

The function can handle large numbers, but extremely large values may require additional tweaks to the VBA code.

What if I only want to convert part of a number, like the decimal?

The provided VBA code already includes logic to handle decimal values, converting them to fraction words.

How do I troubleshoot if the function isn’t working?

If the function isn’t working, check for any typos in the VBA code and ensure macros are enabled. Revisit each step carefully.

Summary

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

Conclusion

Converting digits to words in Excel might seem like a daunting task, but with the help of a custom VBA function, it becomes a breeze. This guide walked you through the process step-by-step, from opening the VBA editor to using the NumToWords function in your workbook.

By following these instructions, you can easily convert numerical values to their word equivalents, enhancing your Excel skills and making your data presentations more dynamic. For further reading, consider exploring more advanced VBA functions or integrating other custom functions. Got a project coming up? Go ahead, give converting digits to words in Excel a try!

Related posts: