Mastering Number to Words Conversion in Excel: Your Ultimate Guide

Converting numbers to words in Excel is a task many of us encounter, especially when dealing with financial documents like invoices, cheques, or reports. Surprisingly, Excel doesn’t offer a built-in function for this. But don’t worry – it’s a challenge we can easily overcome using either Excel Formulas or Visual Basic for Applications (VBA).

In this comprehensive guide, I’m excited to walk you through the steps of creating formulas and user-defined functions that will spell out numbers, convert amounts into words, and even handle currencies and decimal values with ease. You’ll be able to modify these solutions to fit any numeric value you need to convert!

We’ve crafted simple, effective Excel formulas and VBA functions that you can literally copy and paste into your spreadsheets. Whether you prefer the power of VBA or a pure formula-based approach, we’ve got you covered. You can pass any number to these functions and watch them automatically convert to words.

Here are the methods we'll explore for converting numbers to words in Excel:

• VBA to Convert Number to Words in Excel
• Excel Formula to Convert Spell Numbers to Words in Excel
• Convert Number to Words in Excel using Excel LAMBDA Function

Let's dive in!

Steps to Convert Number to Words in Excel using VBA

Converting numbers to words in Excel is straightforward when you create a custom function in Visual Basic for Applications (VBA). Here are the simple steps:

Step 1: Open VBA Editor To begin, you’ll need to open the VBA editor. The quickest way to do this is by pressing Alt + F11 on your keyboard while in Excel.

Step 2: Insert a New Module Once the VBA editor is open, navigate to Insert > Module in the menu. This action creates a new module where you can write your code.

Step 3: Write the Code/Copy Our NumToWords VBA Code Within this new module, you’ll input the code that creates your custom function for converting numbers to words. You can either write your own code or, even better, use the robust VBA code I’ve provided below.

Step 4: Use the Function After implementing the code, you can now use your new function just like any other built-in Excel function. Simply return to your Excel sheet and in any cell, type =NumToWords(CELL). Remember to replace CELL with the actual cell reference containing the numeric value you wish to convert. For example, =NumToWords(A1) will convert the number in cell A1 to words.

By following these steps, you can easily convert numbers to words in Excel, significantly enhancing your data analysis and reporting capabilities.

VBA to Convert Numbers to Words in Excel

Here's the user-friendly Excel VBA Function I've created to convert numbers to words. Just copy the code below and paste it into your module.

VBA

' VBA Function to Convert Number to Word in Excel
Function NumToWords(ByVal MyNumber, Optional isProper As Boolean = False) As String
    Dim Units As String, SubUnits As String, TempStr As String
    Dim DecimalPlace As Integer, Count As Integer
    Dim Place(9) As String
    
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "
    
    ' Check if the input is empty or not a number
    If IsEmpty(MyNumber) Or Not IsNumeric(MyNumber) Then
        NumToWords = ""
        Exit Function
    End If

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

    ' Handle Zero Case
    If Val(MyNumber) = 0 Then
        NumToWords = "Zero"
        Exit Function
    End If

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

    ' Convert Decimal Part
    If DecimalPlace > 0 Then
        SubUnits = GetDecimalWords(Mid(MyNumber, DecimalPlace + 1), isProper)
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If

    ' Process Integer Part
    Count = 1
    Do While MyNumber <> ""
        TempStr = GetHundreds(Right(MyNumber, 3))
        If TempStr <> "" Then Units = TempStr & Place(Count) & Units
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop

    ' Combine Integer and Decimal Parts
    If SubUnits = "" Then
        NumToWords = Application.Trim(Units)
    Else
        NumToWords = Application.Trim(Units & " Point " & SubUnits)
    End If
End Function

' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
    
    ' Convert Hundreds Place
    If Mid(MyNumber, 1, 1) <> "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
    
    ' Convert 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

    GetHundreds = Result
End Function

' Converts a number from 10 to 99 into text
Function GetTens(TensText)
    Dim Result As String
    Result = ""
    
    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"
        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 "
        End Select
        Result = Result & GetDigit(Right(TensText, 1))
    End If

    GetTens = Result
End Function

' Converts a number from 1 to 9 into text
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

' Converts Decimal Digits to Words
Function GetDecimalWords(DecimalPart As String, isProper As Boolean) As String
    Dim i As Integer, DecWord As String

    If isProper Then
        ' Proper: 25.57 ? "Twenty Five Point Five Seven"
        For i = 1 To Len(DecimalPart)
            If i > 1 Then DecWord = DecWord & " "
            DecWord = DecWord & GetDigit(Mid(DecimalPart, i, 1))
        Next i
    Else
        ' Default: 25.57 ? "Twenty Five Point Fifty Seven"
        DecWord = GetTens(Left(DecimalPart & "00", 2))
    End If

    GetDecimalWords = DecWord
End Function

Now that you’ve defined the function, you can use it just like any other Excel function. You can use it as an Excel formula in a cell or assign it to a variable in a VBA procedure.

Converting Decimal Numbers to Words in Excel

Handling decimal values correctly is crucial when converting numbers into words. My NumToWords function provides an option to spell decimals in two formats:

• Standard Format: 25.57 → “Twenty-Five Point Fifty-Seven”
• Proper Decimal Format: 25.57 → “Twenty-Five Point Five Seven”

You can choose the format by setting the isProper argument:

VBA

MsgBox NumToWords(25.57, True) 'Output: Twenty-Five Point Five Seven
MsgBox NumToWords(25.57, False) 'Output: Twenty-Five Point Fifty-Seven

Key Features of the NumToWords VBA Function

• Converts numbers into words, including whole numbers and decimals.
• Handles large numbers up to trillions with proper formatting.
• Supports proper decimal spelling, allowing options like “Twenty-Five Point Fifty-Seven” or “Twenty-Five Point Five Seven.”
• Eliminates unnecessary spaces for clean and readable output.
• Works as a User-Defined Function (UDF) in Excel for easy use in formulas.
• Optimized for VBA automation, making it incredibly useful for reports and financial documents.

How to Convert Amount into Words in Excel?

Excel doesn't have a built-in function to convert numbers into words, but with a simple VBA function, we can easily convert any amount into words. This is particularly useful for invoices, cheques, and various financial reports.

VBA Function to Convert Amount in Words with Currency and Units

To convert numbers into words along with currency, you can use a custom VBA function. The AmountToWords function allows you to specify the currency and units, making it highly versatile for different financial applications.

VBA

' Function to Convert Number to Words with Currency & Units
Function AmountToWords(ByVal MyNumber, ByVal strCurrency As String, ByVal strUnits As String) As String
    Dim IntegerPart As String, DecimalPart As String
    Dim DecimalPlace As Integer

    ' Check if the input is empty or not a number
    If IsEmpty(MyNumber) Or Not IsNumeric(MyNumber) Then
        AmountToWords = ""
        Exit Function
    End If
    
    ' Convert MyNumber to string and trim white space
    MyNumber = Trim(Str(MyNumber))
    
    ' Find position of decimal place (0 if none)
    DecimalPlace = InStr(MyNumber, ".")
    
    ' Get Integer and Decimal Parts
    If DecimalPlace > 0 Then
        IntegerPart = Trim(Left(MyNumber, DecimalPlace - 1))
        DecimalPart = Right(MyNumber, Len(MyNumber) - DecimalPlace)
    Else
        IntegerPart = MyNumber
        DecimalPart = ""
    End If
    
    ' Convert to words
    If IntegerPart <> "" Then
        AmountToWords = NumToWords(IntegerPart) & " " & strCurrency
    End If
    
    If DecimalPart <> "" And Val(DecimalPart) > 0 Then
        If AmountToWords <> "" Then AmountToWords = AmountToWords & " and "
        AmountToWords = AmountToWords & NumToWords(Left(DecimalPart & "00", 2)) & " " & strUnits
    End If
End Function

'****Make sure to have all the functions defined in the NumToWords function in the above section****

Example: The code below spells the amount 100.99 as ‘One Hundred Dollars and Ninety-Nine Cents’.

VBA

MsgBox AmountToWords(100.99, "Dollar", "Cent")

Output: One Hundred Dollars and Ninety-Nine Cents

Convert Amount to Words for Different Currencies in Excel

With the AmountToWords VBA function, you can effortlessly convert numbers into words for various currencies. Here are some examples of how the function handles different amounts:

• Dollars (USD) → =AmountToWords(A1, “Dollars”, “Cents”)
  • 1250.75 → One Thousand Two Hundred Fifty Dollars and Seventy-Five Cents
• Indian Rupees (INR) → =AmountToWords(A1, “Rupees”, “Paise”)
  • 5234.90 → Five Thousand Two Hundred Thirty-Four Rupees and Ninety Paise
• British Pounds (GBP) → =AmountToWords(A1, “Pounds”, “Pence”)
  • 999.99 → Nine Hundred Ninety-Nine Pounds and Ninety-Nine Pence
• Euros (EUR) → =AmountToWords(A1, “Euros”, “Cents”)
  • 1890.50 → One Thousand Eight Hundred Ninety Euros and Fifty Cents
• Canadian Dollars (CAD) → =AmountToWords(A1, “Canadian Dollars”, “Cents”)
  • 437.25 → Four Hundred Thirty-Seven Canadian Dollars and Twenty-Five Cents
• Australian Dollars (AUD) → =AmountToWords(A1, “Australian Dollars”, “Cents”)
  • 320.10 → Three Hundred Twenty Australian Dollars and Ten Cents
• Dirhams (AED) → =AmountToWords(A1, “Dirhams”, “Fils”)
  • 7589.45 → Seven Thousand Five Hundred Eighty-Nine Dirhams and Forty-Five Fils
• Yen (JPY) → =AmountToWords(A1, “Yen”, “”)
  • 15000 → Fifteen Thousand Yen
• Chinese Yuan (CNY) → =AmountToWords(A1, “Yuan”, “Fen”)
  • 682.60 → Six Hundred Eighty-Two Yuan and Sixty Fen

This function simplifies converting amounts into words for invoices, cheques, and financial documents across multiple currencies. 🚀

How to Implement VBA Code for Amount to Words in Excel?

Follow these steps to add the VBA function to your Excel workbook:

1. Open Excel and press ALT + F11 to open the VBA Editor.
2. Click Insert → Module.
3. Copy and paste the AmountToWords VBA function (along with the NumToWords and its helper functions if you haven't already).
4. Close the editor and use the function in a cell: =AmountToWords(A1, "Dollar", "Cent")
5. Press Enter to get the amount in words.

Convert Cheque Amount into Words in Excel

For writing cheque amounts, the AmountToWords function ensures that the correct wording is used. For instance:

=AmountToWords(1500, "Dollar", "Cent") Output: One Thousand Five Hundred Dollars

This ensures the amount is clearly written to avoid any confusion.

Convert Amount in Words for Accounting and Invoices

When preparing invoices and financial documents, having the amount spelled out significantly enhances clarity and accuracy. The VBA function I’ve provided can automatically populate invoice amounts into words, making your financial reporting more professional and error-free.

Here are some examples demonstrating how the AmountToWords VBA function converts amounts into words for different currencies:

• 1250.75 → =AmountToWords(A1, “Dollars”, “Cents”)
  • Output: One Thousand Two Hundred Fifty Dollars and Seventy-Five Cents
• 5234.90 → =AmountToWords(A1, “Rupees”, “Paise”)
  • Output: Five Thousand Two Hundred Thirty-Four Rupees and Ninety Paise
• 999.99 → =AmountToWords(A1, “Pounds”, “Pence”)
  • Output: Nine Hundred Ninety-Nine Pounds and Ninety-Nine Pence
• 1890.50 → =AmountToWords(A1, “Euros”, “Cents”)
  • Output: One Thousand Eight Hundred Ninety Euros and Fifty Cents
• 437.25 → =AmountToWords(A1, “Canadian Dollars”, “Cents”)
  • Output: Four Hundred Thirty-Seven Canadian Dollars and Twenty-Five Cents
• 320.10 → =AmountToWords(A1, “Australian Dollars”, “Cents”)
  • Output: Three Hundred Twenty Australian Dollars and Ten Cents
• 7589.45 → =AmountToWords(A1, “Dirhams”, “Fils”)
  • Output: Seven Thousand Five Hundred Eighty-Nine Dirhams and Forty-Five Fils
• 15000 → =AmountToWords(A1, “Yen”, “”)
  • Output: Fifteen Thousand Yen
• 682.60 → =AmountToWords(A1, “Yuan”, “Fen”)
  • Output: Six Hundred Eighty-Two Yuan and Sixty Fen

By implementing the AmountToWords VBA function, businesses can streamline invoice generation and significantly improve financial accuracy in Excel. 🚀

Key Features of the AmountToWords VBA Function

• Converts numeric amounts into words with currency and unit representation.
• Supports various currencies, including Dollars, Rupees, Pounds, Euros, Yen, etc.
• Handles both whole numbers and decimals, ensuring accurate spelling for cents/paise.
• Automatically formats numbers for invoices, cheques, and financial reports.
• Customizable currency and unit names to fit different financial applications.
• Optimized for Excel VBA, allowing easy integration into spreadsheets and macros.

Fixing Common Errors in VBA Amount to Words Conversion

If you encounter errors while using the function, here are some common solutions:

• Function Not Found?: Ensure the function is added to the VBA editor correctly. Remember to use =AmountToWords(A1, “Dollar”, “Cent”) in Excel.
• Incorrect Spelling of Decimal Numbers?: Set the isProper argument in the NumToWords function to True or False to match the required spelling format.
• Handling Large Numbers?: My functions support numbers up to trillions, ensuring all large amounts are correctly spelled out.

Using NumToWords in Excel Formula to Convert Number to Words

The NumToWords function, once created in Excel VBA, becomes incredibly handy for converting numbers into their word equivalent. This is especially useful for writing checks, generating invoices, or creating specific reports.

The example table below illustrates how to use this function. It features three columns: ‘Number’, ‘Formula’, and ‘Result’. The ‘Number’ column holds your numerical values. The ‘Formula’ column applies the NumToWords function to the numbers in Column A. The ‘Result’ column then displays the output of the function – the verbal representation of the numbers.

To use this function, remember to first implement NumToWords in your Excel VBA as outlined in the prior steps.

=NumToWords(A1)

Number (A)	Formula (B)	Result (C)
1	=NumToWords(A2)	One
23	=NumToWords(A3)	Twenty Three
456	=NumToWords(A4)	Four Hundred Fifty Six
7890	=NumToWords(A5)	Seven Thousand Eight Hundred Ninety
12345	=NumToWords(A6)	Twelve Thousand Three Hundred Forty Five

Your number will be converted to words in the cell where you wrote the formula!

Numbers to Words – VBA Use Cases

The NumToWords function in Excel VBA converts numeric values to words. To use it, you first open the VBA editor (Alt + F11), create a new module, and input the function code. Once it’s implemented, you can leverage it within your VBA scripts.

VBA

strNumberToWord = NumToWords(Range("A1"))

For example:

• Assign it to a variable:
  VBA

  Dim result As String
  result = NumToWords(123)
  

• Show the result in a message box:
  VBA

  MsgBox NumToWords(123)
  

• Enter the result in a cell:
  VBA

  Range("A1").Value = NumToWords(123)
  

  This will enter the converted word form of the number into cell A1. This function truly extends Excel’s capabilities and offers remarkable flexibility.

Remember, user-defined functions in Excel VBA are powerful and flexible tools that allow you to create new functionality, such as converting numbers to words. However, they are a bit more complex and require some knowledge of VBA to create and debug.

Convert Numbers to Words in Excel - Download Example File

I've put together an example file with Excel formulas to convert numbers to words. You can see how the function is used in both Excel formulas and VBA. [Download Example File - Link to your file here]

Convert Amount to Words Using Excel Formula: Spell Numbers in Excel Without VBA

Have you ever needed to spell numbers in Excel for financial reports, invoices, or official documents but preferred to avoid macros? Converting numerical amounts to their word equivalents can significantly enhance the professionalism and clarity of your spreadsheets. While VBA offers a robust solution, not everyone is comfortable with coding. Fortunately, you can achieve number to words in Excel using a comprehensive nested formula, completely without VBA. In this section, I’ll walk you through an effective formula that transforms numbers into words seamlessly.

Why Convert Amount to Words in Excel?

Converting amounts to words is essential in various scenarios, such as:

• Financial Statements: Presenting amounts in words reduces the risk of tampering and provides clearer context.
• Invoices and Bills: Enhances readability and professionalism, making documents easier to understand.
• Legal Documents: Ensures clarity and prevents misunderstandings or misinterpretations.

The Excel Formula to Spell Numbers

Below is a detailed Excel formula I've put together that converts numbers into words without the need for VBA. This formula handles numbers up to 999 billions and accurately accounts for units, tens, hundreds, thousands, millions, and billions.

Formula:

Excel

=IF(A1=0, "Zero",
    TRIM(
        IF(INT(A1/1000000000) > 0,
            IF(INT(A1/1000000000 / 100) > 0,
                LOOKUP(INT(A1/1000000000 / 100), {1,2,3,4,5,6,7,8,9},
                    {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"}) & " Hundred ",
                "") &
            IF(MOD(INT(A1/1000000000),100) < 20, LOOKUP(MOD(INT(A1/1000000000),100), {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19}, {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"}) & " ", LOOKUP(INT(MOD(INT(A1/1000000000),100)/10), {2,3,4,5,6,7,8,9}, {"Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"}) & IF(MOD(INT(A1/1000000000),10) >0,
                    " " & LOOKUP(MOD(INT(A1/1000000000),10),
                        {1,2,3,4,5,6,7,8,9},
                        {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"}),
                    "")
            ) & " Billion ",
            "") &
        IF(INT(MOD(A1,1000000000)/1000000) > 0,
            IF(INT(MOD(A1,1000000000)/1000000 / 100) > 0,
                LOOKUP(INT(MOD(A1,1000000000)/1000000 / 100), {1,2,3,4,5,6,7,8,9},
                    {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"}) & " Hundred ",
                "") &
            IF(MOD(INT(MOD(A1,1000000000)/1000000),100) < 20, LOOKUP(MOD(INT(MOD(A1,1000000000)/1000000),100), {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19}, {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"}) & " ", LOOKUP(INT(MOD(INT(MOD(A1,1000000000)/1000000),100)/10), {2,3,4,5,6,7,8,9}, {"Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"}) & IF(MOD(INT(MOD(A1,1000000000)/1000000),10) >0,
                    " " & LOOKUP(MOD(INT(MOD(A1,1000000000)/1000000),10),
                        {1,2,3,4,5,6,7,8,9},
                        {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"}),
                    "")
            ) & " Million ",
            "") &
        IF(INT(MOD(A1,1000000)/1000) > 0,
            IF(INT(MOD(A1,1000000)/1000 / 100) > 0,
                LOOKUP(INT(MOD(A1,1000000)/1000 / 100), {1,2,3,4,5,6,7,8,9},
                    {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"}) & " Hundred ",
                "") &
            IF(MOD(INT(MOD(A1,1000000)/1000),100) = 0, "",
                IF(MOD(INT(MOD(A1,1000000)/1000),100) < 20, LOOKUP(MOD(INT(MOD(A1,1000000)/1000),100), {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19}, {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"}) & " ", LOOKUP(INT(MOD(INT(MOD(A1,1000000)/1000),100)/10), {2,3,4,5,6,7,8,9}, {"Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"}) & IF(MOD(INT(MOD(A1,1000000)/1000),10) >0,
                    " " & LOOKUP(MOD(INT(MOD(A1,1000000)/1000),10),
                        {1,2,3,4,5,6,7,8,9},
                        {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"}),
                    "")
                )
            ) & " Thousand ",
            "") &
        IF(INT(MOD(A1,1000)/100) > 0,
            LOOKUP(INT(MOD(A1,1000)/100), {1,2,3,4,5,6,7,8,9},
                {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"}) & " Hundred ",
            "") &
        IF(MOD(A1,100) = 0, "",
            IF(MOD(A1,100) < 20, LOOKUP(MOD(A1,100), {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19}, {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine","Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"}), LOOKUP(INT(MOD(A1,100)/10), {2,3,4,5,6,7,8,9}, {"Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"}) & IF(MOD(A1,10) > 0,
                " " & LOOKUP(MOD(A1,10), {1,2,3,4,5,6,7,8,9},
                    {"One","Two","Three","Four","Five","Six","Seven","Eight","Nine"}),
                "")
            )
        )
    )
)

Key Features:

• ✅ Handles all number ranges (0 to 999,999,999,999).
• ✅ Correctly spells numbers including units, tens, hundreds, thousands, millions, and billions.
• ✅ Supports special cases such as numbers ending in 00 (e.g., 100, 2000, 1000000).
• ✅ Handles tens and hundreds in billions correctly (10,000,000,000 → “Ten Billion”).
• ✅ Removes extra spaces using TRIM() for a clean output.

How the Formula Works:

• Check for Zero: If the value in cell A1 is 0, the formula returns “Zero”.
• Billions: Extracts the billions place and appends “Billion” if applicable.
• Millions: Extracts the millions place and appends “Million” if applicable.
• Thousands: Handles the thousands segment, including hundreds within thousands. It distinguishes between numbers below 20 (which have unique names) and multiples of ten.
• Hundreds: Processes the hundreds place within the remaining number.
• Tens and Units: Converts the last two digits into words, handling both teens and standard tens.
• TRIM Function: Removes any extra spaces from the final output for a clean result.

Example:

Let’s see how the formula works with an example.

• Number: 7,804,033
• Expected Output: “Seven Million Eight Hundred Four Thousand Thirty Three”
• Formula Output: “Seven Million Eight Hundred Four Thousand Thirty Three”

Another example:

• Number: 99,999
• Expected Output: “Ninety Nine Thousand Nine Hundred Ninety Nine”
• Formula Output: “Ninety Nine Thousand Nine Hundred Ninety Nine”

Benefits of Using This Formula

• No VBA Required: Perfect for users who prefer to avoid macros or have restrictions on using VBA.
• Comprehensive Coverage: Handles numbers up to billions, making it suitable for most financial and official documents.
• Reusable: Easily apply the formula across multiple cells to convert various numbers.

Limitations

• Complexity: The formula is lengthy and can be challenging to modify for those unfamiliar with nested functions.
• Performance: For extremely large datasets, the formula might impact Excel’s performance.
• Static Structure: It doesn’t inherently handle decimal points or negative numbers. Additional logic would be required for such cases.

Tips for Using the Formula

• Cell Reference: Always replace A1 with the cell reference containing the number you want to convert.
• Copying the Formula: Due to its complexity, ensure that the formula is copied correctly without missing any parts.
• Formatting: Ensure that the number is in a numerical format without any special characters or spaces.
• Testing: Test the formula with various numbers to ensure it meets your requirements.

Excel Formula to Convert Numbers to Words Using LAMBDA Function

Update: Now You Can Use the Newly Introduced LAMBDA Function!

In addition to the VBA code we’ve previously discussed for converting numbers to words in Excel, I'm thrilled to introduce a powerful and efficient method using the new LAMBDA function. This method simplifies the process, eliminating the need for VBA and allowing you to achieve the same results directly within your worksheet. The LAMBDA function, a recent addition to Excel, enables you to define custom functions using Excel formulas, making your workbook even more dynamic and versatile.

The LAMBDA Formula

Below is the LAMBDA function I’ve developed that converts numbers to words for values ranging from 0 up to 999 billion. This formula is comprehensive and handles various numeric ranges including units, tens, hundreds, thousands, and millions.

Excel

=LAMBDA(num,
    LET(
        singleDigits, {"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"},
        
        units, MOD(num, 10),
        tensPlace, MOD(INT(num / 10), 10),
        hundredsPlace, MOD(INT(num / 100), 10),
        thousandsPlace, MOD(INT(num / 1000), 1000),
        millionsPlace, MOD(INT(num / 1000000), 1000),
        billionsPlace, INT(num / 1000000000),

        words,
        IF(
            num = 0, "Zero",
            IF(
                num < 10, INDEX(singleDigits, num + 1),
                IF(
                    num < 20, INDEX(teens, num - 9),
                    IF(
                        num < 100, INDEX(tens, tensPlace + 1) & IF(units <> 0, " " & INDEX(singleDigits, units + 1), ""),
                        IF(
                            num < 1000, INDEX(singleDigits, hundredsPlace + 1) & " Hundred" & IF(MOD(num, 100) <> 0, " " & NUM2WORDS(MOD(num, 100)), ""),
                            IF(
                                num < 1000000, NUM2WORDS(INT(num / 1000)) & " Thousand" & IF(MOD(num, 1000) <> 0, " " & NUM2WORDS(MOD(num, 1000)), ""),
                                IF(
                                    num < 1000000000, NUM2WORDS(INT(num / 1000000)) & " Million" & IF(MOD(num, 1000000) <> 0, " " & NUM2WORDS(MOD(num, 1000000)), ""),
                                    IF(
                                        num < 1000000000000, NUM2WORDS(billionsPlace) & " Billion" & IF(MOD(num, 1000000000) <> 0, " " & NUM2WORDS(MOD(num, 1000000000)), ""),
                                        "Number out of range"
                                    )
                                )
                            )
                        )
                    )
                )
            )
        ),
        words
    )
)

Steps to Define the LAMBDA Function

1. Open Excel.
2. Go to the “Formulas” tab.
3. Click on “Name Manager”.
4. Click on “New” to create a new named formula.
5. In the “Name” field, enter NUM2WORDS.
6. In the “Refers to” field, paste the above LAMBDA function.

Using the NUM2WORDS Function

Once you’ve defined the NUM2WORDS named formula, you can use it in any cell within your worksheet. Simply enter the formula =NUM2WORDS(cell_reference) where cell_reference is the cell containing the number you want to convert. For example:

=NUM2WORDS(A1)

This function will convert the numeric value in cell A1 to its corresponding word form, providing a seamless and automated way to enhance your Excel projects.

• If you type the formula =NUM2WORDS(35500), it will give you: Thirty-Five Thousand Five Hundred
• If Range A1=5050000, it will output: Five Million Fifty Thousand.

You can customize this to suit your needs based on your data!

Note: Maximum Range This LAMBDA function supports converting numbers up to 999 billion. If you need to handle larger numbers, you will need to extend the logic further to accommodate those values.

Download Example File – Excel Formula to Convert Number to Words

Get a ready-to-use Excel file 🚀 that converts numbers into words using formulas—no VBA required! This file helps you quickly spell out numbers for invoices, cheques, and reports. [Link to your download file for the Excel Formula version]

Conclusion

Converting numbers to words in Excel is indeed essential for financial reports, invoices, and legal documents, despite Excel lacking a built-in function for this. In this blog post, I've provided multiple powerful solutions to achieve this, including a robust VBA function (NumToWords and AmountToWords), a comprehensive Excel formula, and an efficient LAMBDA function to spell out numbers automatically.

With these methods, you can:

• ✔ Convert numbers to words automatically in Excel.
• ✔ Handle large numbers and decimal values with proper formatting.
• ✔ Use different currencies and units with the AmountToWords function.
• ✔ Apply the formula or VBA function in invoices, cheques, and reports seamlessly.

To simplify the process, I encourage you to download the example files and start using these functions today! 🚀

Note: Keep in mind that the provided VBA function for NumToWords will primarily work with whole numbers up to approximately 999,999,999,999,999. While it has decimal handling, for extremely large numbers or highly complex decimal requirements, the code might need further modification.

I truly hope you found this post on converting numbers to words in Excel valuable and practical. Your thoughts and feedback are incredibly important to me!

If you’ve tried these techniques or have any questions, please feel free to share your experiences in the comments below. I'd love to hear how these functions have worked for you or if you've discovered any other useful methods.

Your input helps me make my content better and more relevant to your needs. Additionally, if you’ve encountered any issues or difficulties along the way, please share those as well so I can improve my instructions.

Thank you again for reading my blog. Your engagement helps me create more useful content for you and other readers!