Convert Number to Words in Excel

Converting number to words in Excel is a common task, but Excel doesn’t have the built-in Function for this. However, it can be easily accomplished using Excel Formulas or with the help of Visual Basic for Applications (VBA). In this blog post, we will walk you through the steps of creating Formulas and user-defined functions that Spell Number in Excel, Convert Amount in words in Excel. You can modify the Formulas and Functions to converts any numeric value, currencies, Decimal Values to words.

We created Simple Excel Formulas to Convert Numbers to Words. You can simply Copy and Paste the Formulas or VBA Function and use it in both Excel Formulas and VBA Procedures. You can Pass any Number to the Function to Spell Number in Words. You can convert numbers to words in excel automatically using the following methods:

• 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

Steps to Convert Number to Words in Excel using VBA

Here are the simple steps for Converting numbers to words in Excel can be done by creating a custom function in Visual Basic for Applications (VBA).

1. Step 1: Open VBA Editor: To start the process, you will first need to open the VBA editor. This can be accomplished by pressing Alt + F11 on your keyboard while in Excel.
2. Step 2: Insert a New Module: Once the VBA editor is open, you should navigate to Insert > Module in the menu. This will create a new module where you can write your code.
3. Step 3: Write the Code/Copy Our NumToWords VBA Code: Within this new module, you will need to input the code that will create your custom function for converting numbers to words. You can either write your own code or use the VBA code provided in the previous section of this post.
4. Step 4: Use the Function: After the code is implemented, you can now use the new function just like any other built-in function in Excel. Simply return to your Excel sheet and in any cell, write =NumToWords(CELL). You should replace CELL with the cell number that contains the numeric value you wish to convert. For example, =NumToWords(A1) will convert the number in cell A1 to words.

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

VBA to Convert Numbers to Word in Excel

Here is the easy to use Excel VBA Function to convert number to words. You can copy the below code and paste in your code module.

' 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 have defined the function, you can use it just like any other Excel function. You can use it as Excel formula in a Cell or you can assign to a variable in VBA.

Converting Decimal Numbers to Words in Excel

Handling decimal values correctly is important when converting numbers into words. The 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:

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 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 useful for reports and financial documents.

How to Convert Amount into Words in Excel?

Excel does not have a built-in function to convert numbers into words, but with a simple VBA function, you can easily convert any amount into words. This is especially useful for invoices, cheques, and 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 versatile for different financial applications.

' 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 below code spells the amount 100.99 as ‘One Hundred Dollars and Ninety Nine Cents’

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 easily convert numbers into words for different currencies. Below are some examples of how the function converts various amounts into words:

• 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 makes it easy to convert 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 in Excel:

1. Open Excel and press ALT + F11 to open the VBA Editor.
2. Click Insert → Module.
3. Copy and paste the AmountToWords VBA function.
4. Close the editor and use the function in a cell:

=AmountToWords(A1, "Dollar", "Cent")

1. 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. Example:

=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 helps in clarity and accuracy. The VBA function can be used to automatically populate invoice amounts into words, making financial reporting more professional and error-free.

Below are some examples showing 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 improve financial accuracy in Excel. 🚀

Key Features of 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 solutions:

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

Using NumToWords in Excel Formula to Convert Number to Words

The NumToWords function, created in Excel VBA, converts numbers into their word equivalent. This is especially handy for writing checks, generating invoices, or creating certain reports. The example table provided uses this function. It has three columns: ‘Number’, ‘Formula’, and ‘Result’. ‘Number’ holds the numerical values. ‘Formula’ applies the NumToWords function to the numbers in Column A. ‘Result’ shows 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)

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 use it within your VBA scripts.

strNumberToWord=NumToWords(Range("A1"))

For example:

1. Assign it to a variable:
   • Dim result As String
   • result = NumToWords(123)
2. Show the result in a message
   • MsgBox NumToWords(123)
3. Enter the result in a cell,
   • Range("A1").Value = NumToWords(123)
   • which will enter the converted word form of the number into cell A1. This function extends Excel’s capabilities and offers more 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 knowledge of VBA to create and debug.

Convert Numbers to Words in Excel -Download Example File

Here is the Example file with Excel formula to Convert Number to Words. You can see the Example of Function using in both Excel Formula and VBA.

Convert Numbers to Words in Excel VBA

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? Converting numerical amounts to their word equivalents can enhance the professionalism and clarity of your spreadsheets. While VBA (Visual Basic for Applications) offers a robust solution, not everyone is comfortable with coding. Fortunately, you can achieve number to words in Excel formula without VBA using a comprehensive nested formula. In this blog post, we’ll walk you through an effective formula that transforms numbers into words seamlessly.

Why Convert Amount to Words in Excel?

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

• Financial Statements: Presenting amounts in words reduces the risk of tampering.
• Invoices and Bills: Enhances readability and professionalism.
• Legal Documents: Ensures clarity and prevents misunderstandings.

The Excel Formula to Spell Numbers

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

Formula:

=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 (10000000000 → “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.
• Distinguishes between numbers below 20 (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: 7804033
• Expected Output: “Seven Million Eight Hundred Four Thousand Thirty Three”
• Formula Output: “Seven Million Eight Hundred Four Thousand Thirty Three”

Another example:

• Number: 99999
• 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 handle decimal points or negative numbers. Additional logic is required for such cases.

Tips for Using the Formula

• Cell Reference: 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, we now 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 more dynamic and versatile.

The LAMBDA Formula

Below is the LAMBDA function 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.

=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

• Open Excel.
• Go to the “Formulas” tab.
• Click on “Name Manager”.
• Click on “New” to create a new named formula.
• In the “Name” field, enter NUM2WORDS.
• 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 to suite 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

Convert Numbers to Words in Excel Formula

Conclusion

Converting numbers to words in Excel is essential for financial reports, invoices, and legal documents, yet Excel lacks a built-in function for this. In this blog post, we provided multiple solutions to achieve this, including a VBA function (NumToWords and AmountToWords), an Excel formula, and a LAMBDA function to spell out numbers efficiently.

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, download the example files and start using these functions today! 🚀

Note: Keep in mind that this VBA function will only work with whole numbers between 0 and 999999999999999. If you need to work with very large numbers or decimals, the code would need to be modified.

We hope you found our post on converting numbers to words in Excel valuable and practical. Your thoughts and feedback are extremely important to us!

If you’ve tried this technique or have any questions about it, please feel free to share your experiences in the comments below. We’d love to hear how this function has worked for you or if there are any other methods you’ve found useful.

Your input helps us make our content better and more relevant to your needs. Additionally, if you’ve encountered any issues or difficulties along the way, share those as well so we can improve our instructions.
Thank you again for reading our blog. Your engagement helps us create more useful content for you and other readers.