How to Convert Numbers to Words in Excel: A Complete Guide

Number to Words in Excel - Excel Point Nepal

Introduction

Converting numbers to words in Excel is a common requirement, especially in financial and legal documents where monetary values need to be displayed in text format. While Excel doesn’t offer a built-in function for this purpose, there are several methods you can use to achieve this functionality. In this guide, we’ll explore how to convert numbers to words in Excel using formulas, VBA code, and add-ins.

Why Convert Numbers to Words in Excel?

Converting numbers to words in Excel is essential for:

  • Financial Documentation: Displaying amounts in words on invoices and checks to prevent ambiguity.
  • Data Validation: Ensuring accuracy by cross-referencing numerical data with its textual representation.
  • Professional Reporting: Enhancing the readability and professionalism of reports and official documents.

Methods to Convert Numbers to Words in Excel

1. Using Excel Formulas (Without VBA)

Although Excel doesn’t have a direct function to convert numbers to words, you can create a complex formula. This method involves nesting multiple functions like CHOOSE, MOD, and INT to map numbers to their corresponding words. However, this approach is intricate and not easily scalable for large numbers or frequent use.

2. Using VBA Code

Visual Basic for Applications (VBA) allows you to create custom functions in Excel. By writing a VBA function, you can automate the conversion of numbers to words. This method is efficient and reusable across your Excel workbooks.

Steps to Implement VBA Code:

  1. Open the VBA Editor:
    • Press Alt + F11 to open the VBA editor.
    • Click on Insert > Module to create a new module.
Function NumberToWords(ByVal MyNumber)
    Dim Units As String
    Dim Tens As String
    Dim Hundreds As String
    Dim Thousands As String
    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 Trim white space
    MyNumber = Trim(CStr(MyNumber))

    ' Find Position of decimal place
    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

    NumberToWords = 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)
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
    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
  1. Write the Function:
    • Paste the VBA code that converts numbers to words into the module.
  2. Use the Function:
    • In your Excel worksheet, use the function by typing =NumberToWords(cell_reference).

This method provides a customizable solution to convert numbers to words in Excel.

3. Using Excel Add-Ins

Several third-party add-ins can simplify converting numbers to words in Excel. These add-ins integrate seamlessly with Excel and provide user-friendly interfaces.

How to Install an Add-In:

  1. Download the Add-In:
    • Obtain the add-in file (.xlam or .xla) from a trusted source.
  2. Install the Add-In:
    • Go to File > Options > Add-Ins.
    • Click on Go next to Manage Excel Add-ins.
    • Browse and select the downloaded add-in file.
    • Ensure the add-in is checked in the list and click OK.

Using add-ins is a convenient method, especially for users unfamiliar with VBA programming.

Considerations When Choosing a Method

  • Complexity: VBA requires programming knowledge, and formulas can be cumbersome. Add-ins offer a user-friendly alternative.
  • Compatibility: Ensure that the chosen method is compatible with your version of Excel and complies with your organization’s security policies.
  • Performance: For large datasets, VBA functions and add-ins may perform better than complex formulas.

Conclusion

Converting numbers to words in Excel enhances the clarity and professionalism of your documents. Whether you choose to use complex formulas, VBA code, or add-ins, each method has its advantages. Assess your specific needs and resources to select the most suitable approach. By implementing these techniques, you can streamline your workflow and improve the accuracy of your Excel reports.

Keywords Used:

  • Convert numbers to words in Excel
  • Number to text in Excel
  • Excel number to words function
  • VBA convert number to words
  • Excel add-ins for number to words conversion

For more Excel tips and tutorials, visit Excel Point Nepal.

Advanced Excel Online Live Class Available

Number to Words - Advanced Excel online live class in Excel Point Nepal

In the above post, we covered these Queries:

number to words in excel

convert number to words in excel

how to convert number to words in excel

number to words in excel rupees

number to words excel formula

excel number to words formula

number to words in excel without vba

number to words vba code

number to words in excel add-in

convert numbers to words in excel online

excel function to convert number to words

convert number to words vba code

excel formula to convert number to words

number to words in excel 2010

number to words in excel 2013

number to words in excel 2016

number to words in excel 2007

number to words formula in excel without vba

how to convert number to word in excel online

number to words converter in excel formula

number to words excel vba code

excel number to words converter

how to write numbers in words in excel

excel convert numbers to words

formula to convert number to words in excel

numbers to words converter in excel

excel function to convert number to words

excel number to word

excel number to words rupees formula

how to convert amount in number to words in excel

how to convert number into words in excel formula

shortcut key to convert number to words in excel

excel how to convert number to words

excel number to word converter

convert number to words formula excel

how to make number to words in excel

microsoft excel convert number to words

how to write number in words in excel

how to convert number to words in excel rupees

convert number to words excel formula

excel formula for converting number to words

number to words converter formula excel

number to word excel vba code

how to get number in words in excel

number to words in english in excel

how to convert number into words in excel formula

convert number to words in excel without vba

convert number to words in excel 2007

number to rupees words in excel

excel formula for converting number to words

how to convert number to words in excel without spellnumber formula

Comments are closed.