How To Convert Numbers Into Words In Excel? Key Methods

0 min read

Converting numbers to words in Excel is essential for creating invoices, reports, and documentation. This article will provide step-by-step instructions, ensuring even those with limited Excel experience can follow along easily.

How to Convert Numbers to Words in Excel using VBA Code?

VBA is a programming language within Excel that can be used to automate various tasks, including converting numbers to words. If you frequently need to convert numbers into words, creating a custom function through VBA is a highly effective solution.

Steps to Convert Numbers into Words Using VBA

1. Open the Excel Workbook where you want to apply the conversion.

2. Open the VBA Editor

How to open VBA Editor?

Open Excel and open your workbook.

Click on the "Developer" tab in the Excel ribbon.

If you don’t see the Developer tab, you can enable it by going to File > Options > Customize Ribbon, then check the Developer option.

In the Developer tab, click on the "Visual Basic" button, which will open the VBA Editor.

3. Insert a New Module:

In the VBA editor, click Insert > Module. This will open a blank module where you can paste the code.

4. Copy and Paste the VBA Code:

Paste the following VBA code into the module:

Function NumberToWords(ByVal MyNumber)
    Dim Units As String
    Dim Tens As String
    Dim Hundreds As String
    Dim Thousands As String
    Dim Temp
    Dim DecimalPlace, Count

    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "
    
    ' Convert MyNumber to string in case it's not
    MyNumber = Trim(CStr(MyNumber))
    
    DecimalPlace = InStr(MyNumber, ".")
    If DecimalPlace > 0 Then
        Temp = Left(MyNumber, DecimalPlace - 1)
    Else
        Temp = MyNumber
    End If
    
    Count = 1
    Do While Temp <> ""
        Hundreds = Right(Temp, 3)
        If Len(Temp) > 3 Then
            Temp = Left(Temp, Len(Temp) - 3)
        Else
            Temp = ""
        End If
        If Hundreds <> "" Then
            Units = ConvertHundreds(CInt(Hundreds))
            NumberToWords = Units & Place(Count) & NumberToWords
        End If
        Count = Count + 1
    Loop
    
    If DecimalPlace > 0 Then
        NumberToWords = NumberToWords & " And Cents " & Mid(MyNumber, DecimalPlace + 1) & "/100"
    End If
End Function

Function ConvertHundreds(ByVal Number)
    Dim Result As String
    Dim T As String
    Dim U As String
    Dim Ones(9) As String
    Dim Tens(9) As String

    ' Units mapping
    Ones(0) = ""
    Ones(1) = "One"
    Ones(2) = "Two"
    Ones(3) = "Three"
    Ones(4) = "Four"
    Ones(5) = "Five"
    Ones(6) = "Six"
    Ones(7) = "Seven"
    Ones(8) = "Eight"
    Ones(9) = "Nine"

    ' Tens mapping
    Tens(2) = "Twenty"
    Tens(3) = "Thirty"
    Tens(4) = "Forty"
    Tens(5) = "Fifty"
    Tens(6) = "Sixty"
    Tens(7) = "Seventy"
    Tens(8) = "Eighty"
    Tens(9) = "Ninety"

    If Number >= 100 Then
        U = Ones(Int(Number / 100)) & " Hundred "
        Number = Number Mod 100
    End If
    
    If Number >= 20 Then
        T = Tens(Int(Number / 10)) & " "
        Number = Number Mod 10
    End If
    
    Result = U & T & Ones(Number)
    ConvertHundreds = Result
End Function

5. Close the VBA Editor:

Once the code is pasted, press Alt + Q to return to the Excel workbook.

6. Use the Custom Function:

Now, in any cell, you can use the custom function =NumberToWords(A1) where A1 contains the number you want to convert.

For example, if A1 contains the number 1234, this formula will output “One Thousand Two Hundred Thirty-Four.”

How to Convert Numbers into Words in Excel: Manual Formula for Small Numbers

If you're only working with small numbers, you can use a nested IF formula. This approach is tedious but works for limited ranges.

=IF(A1=1,"One",IF(A1=2,"Two",IF(A1=3,"Three",...)))

Excel Formula Alternatives: Using LAMBDA for Conversion

Using the LAMBDA function in Excel, you can create a custom formula to convert numbers to words. While Excel doesn't have a built-in function for this, the LAMBDA function allows you to define your own reusable function. Here's a step-by-step guide to create a LAMBDA function for number-to-words conversion.

Steps to Create a LAMBDA Function for Number-to-Words Conversion

1. Define the LAMBDA Function

• Open Excel and select an empty cell.
• Create the LAMBDA function by using a combination of helper functions like IF, TEXT, MID, and LEN. However, converting large numbers to words in Excel can get complex, so you can start with a simple LAMBDA to handle numbers up to 999.

2. Build the LAMBDA Formula

Here’s an example LAMBDA function that can convert numbers up to 999 into words.

=LAMBDA(n,

IF(n=0, "Zero",

IF(n<20, CHOOSE(n, "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen"),

IF(n<100, CHOOSE(INT(n/10), "", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety") & IF(MOD(n,10)>0, " " & TEXT(MOD(n,10), "0"), ""),

CHOOSE(INT(n/100), "One Hundred", "Two Hundred", "Three Hundred", "Four Hundred", "Five Hundred", "Six Hundred", "Seven Hundred", "Eight Hundred", "Nine Hundred") & IF(MOD(n,100)>0, " " & LAMBDA(n, ...)(MOD(n,100)), "")

))))

3. Name the LAMBDA Function

Once the LAMBDA function is working correctly, you can name it for easy reuse:

• Go to Formulas in the ribbon.
• Select Name Manager.
• Click New and give your LAMBDA function a name like NumberToWords.
• Paste the LAMBDA function into the "Refers to" field and click OK.

4. Use the LAMBDA Function

To use the LAMBDA function that you have created, follow these steps:

After defining and naming your LAMBDA function (e.g., NumberToWords), call it directly within a cell:

=NumberToWords(A1)

Where A1 contains the number you want to convert to words.

Why Use LAMBDA?

• LAMBDA allows you to create custom functions without needing VBA.
• It enhances productivity as you can name the function and reuse it across your workbook.

Final Remarks

The methods outlined for converting numbers to words in Excel provide a comprehensive toolkit for users. From utilizing the built-in SpellNumber function to employing VBA macros and alternative formulas, each approach offers unique advantages. Specialized add-ins further enhance functionality, catering to diverse user needs.

Learning these techniques empowers individuals to streamline their data presentation. By implementing these solutions, they can improve clarity and professionalism in their spreadsheets. Exploring these options will yield significant benefits in various applications, from financial reports to academic projects. Readers are encouraged to experiment with these tools and enhance their Excel proficiency.

Quiz Time!!!

Frequently Asked Questions

Q1. How can I convert numbers to words in Excel?

To convert numbers to words in Excel, users can utilize the SpellNumber function, VBA macros, or specific add-ins designed for this purpose. Each method offers a unique approach depending on user preferences and requirements.

Q2. Is there a built-in function in Excel for converting numbers to words?

Excel does not have a built-in function for converting numbers to words. Users typically rely on custom VBA functions or third-party add-ins to achieve this functionality.

Q3. What is the SpellNumber function?

The SpellNumber function is a user-defined function (UDF) that converts numeric values into their corresponding word format. This function must be created using VBA and can be used like any standard Excel formula.

Q4. Can I use VBA macros for conversion?

Yes, VBA macros are an effective way to convert numbers to words in Excel. Users can write a macro that takes a number as input and returns its word representation.

Q5. Can I convert decimal numbers to words in Excel?

Yes, users can modify the SpellNumber function or VBA macros to handle decimal numbers as well. This allows for the conversion of both whole numbers and decimal values into words effectively.

Suggested Reads: 

• What Is A Macro In Excel: Create And Manage Them Effectively
• Tick Symbol In Excel: Common Techniques To Add A Check Mark
• Ageing Formula In Excel: Know All Formulae With Examples
• How To Unhide Columns In Excel: Explore Basic & Advanced Methods