Converting numbers 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 with the help of Visual Basic for Applications (VBA). In this blog post, we will walk you through the steps of creating a user-defined function that converts a numeric value to words.

We created Simple Excel Formula to Convert Numbers to Words. You can simply Copy and Paste the 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.

Steps to Convert Numbers to Words

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 Words

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 to Convert Numbers to Words

Option Explicit

'Main Function to Convert Number to Words
Function NumToWords(ByVal MyNumber)
Dim Units As String
Dim SubUnits 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(Str(MyNumber))

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

' Convert SubUnits and set MyNumber to Units amount.
If DecimalPlace > 0 Then
SubUnits = 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))
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

NumToWords = Application.Trim(Units & SubUnits)
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 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
GetHundreds = Result
End Function

' Converts a number from 10 to 99 into text.
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

' 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

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.

Excel Formula to Convert Numbers 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)

Excel Formula to Convert Numbers to Words

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

=strNumberToWord=NumToWords(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

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 billions and accounts for units, tens, hundreds, thousands, millions, and billions.

Formula:

=IF(A1=0, "Zero",
    TRIM(
        IF(INT(A1/1000000000) > 0,
            LOOKUP(INT(A1/1000000000), {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,
            LOOKUP(INT(MOD(A1,1000000000)/1000000), {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) < 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) < 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"}),
                "")
        )
    )
)

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 10 million. 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, INT(num / 1000000),
        
        words,
        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 < 10000000, NUM2WORDS(millionsPlace) & " Million" & IF(MOD(num, 1000000) <> 0, " " & NUM2WORDS(MOD(num, 1000000)), ""),
                                "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 10 million. If you need to handle larger numbers, you will need to extend the logic further to accommodate those values.

Conclusion

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.

Subscribe To Our Newsletter

Receive all of the latest news, templates and updates fresh from Excelx.com!

Convert Numbers to Words in Excel

Share This Story, Choose Your Platform!

20 Comments

  1. saleem March 29, 2024 at 6:54 am - Reply

    your VBA code is working ggreat, great Job
    Thank you for all you support!

  2. Isaac April 14, 2024 at 6:20 am - Reply

    will it work with currency?

    • PNRao April 21, 2024 at 7:27 am - Reply

      Yes! You can format the data using Excel Number Formatting (Currency).

  3. Sudantha June 25, 2024 at 6:52 am - Reply

    When I type that formula I got the answer as “#NAME?

    • PNRao June 27, 2024 at 10:16 am - Reply

      Please make sure to insert a module and paste the provided VBA code.

  4. DES July 8, 2024 at 6:57 am - Reply

    How to implement for decimals?

    • Jayaprakash September 17, 2024 at 11:43 am - Reply

      I can’t able to convert in rupees

  5. deepak siani July 13, 2024 at 8:13 am - Reply

    can give a formula of convert number to hindi word

  6. Myriam August 29, 2024 at 11:03 pm - Reply

    I followed the steps for the Lambda code in Excel 365 but when I enter the formula it does not recognize it as a formula. It looks like a text.

    • PNRao September 22, 2024 at 4:12 am - Reply

      Please make sure that you have Microsoft 365 installed in your PC.

  7. ahmed hassanin September 1, 2024 at 12:36 pm - Reply

    thank you
    it help

    • PNRao September 22, 2024 at 4:16 am - Reply

      You’re welcome, we are glad it helped!

  8. SharmaJikabeta September 2, 2024 at 12:32 pm - Reply

    For INR (₹) user’s

    Function InWord(ByVal pNumber)
    Dim Dollars As String, Cents As String
    Dim arr()
    arr = Array(“”, “Thousand”, “Lakh”, “Crore”)
    pNumber = Trim(Str(pNumber))

    ‘ Check for decimal part and handle paisa
    Dim xDecimal As Integer
    xDecimal = InStr(pNumber, “.”)
    If xDecimal > 0 Then
    Cents = GetTens(Left(Mid(pNumber, xDecimal + 1) & “00”, 2))
    pNumber = Trim(Left(pNumber, xDecimal – 1))
    End If

    ‘ Process integer part by splitting into groups
    Dim xIndex As Integer
    Dim GroupCount As Integer
    GroupCount = 0
    Do While Len(pNumber) > 0
    Dim xHundred As String, xValue As String
    If GroupCount = 0 Then
    xValue = Right(pNumber, 3) ‘ First three digits from the right
    pNumber = Left(pNumber, Len(pNumber) – 3)
    Else
    xValue = Right(pNumber, 2) ‘ Next two digits
    pNumber = Left(pNumber, Len(pNumber) – 2)
    End If

    xHundred = “”
    If Val(xValue) 0 Then
    If Len(xValue) = 3 Then
    xHundred = GetDigit(Left(xValue, 1)) & ” Hundred ” & GetTens(Right(xValue, 2))
    Else
    xHundred = GetTens(xValue)
    End If
    End If

    If xHundred “” Then
    Dollars = xHundred & ” ” & arr(GroupCount) & ” ” & Dollars
    End If

    GroupCount = GroupCount + 1
    Loop

    ‘ Clean up and format the result
    Dollars = Replace(Trim(Dollars), ” “, ” “)

    Select Case Dollars
    Case “”
    Dollars = “Rupees Only”
    Case “One”
    Dollars = “One Rupee”
    Case Else
    Dollars = Dollars & ” Rupees”
    End Select

    Select Case Cents
    Case “”
    Cents = “”
    Case “One”
    Cents = ” and One Paisa only”
    Case Else
    Cents = ” and ” & Cents & ” Paisa only”
    End Select

    InWord = Dollars & Cents
    End Function

    Function GetTens(pTens)
    Dim Result As String
    Result = “”
    If Val(Left(pTens, 1)) = 1 Then
    Select Case Val(pTens)
    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
    Select Case Val(Left(pTens, 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(pTens, 1))
    End If
    GetTens = Result
    End Function

    Function GetDigit(pDigit)
    Select Case Val(pDigit)
    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

  9. willin October 18, 2024 at 6:21 am - Reply

    hi, what formular spellnumber for no 30.79 in word thirty and cents seventy-nine only

  10. Chan Nan Chew November 19, 2024 at 9:58 am - Reply

    Hi Sir, how to modify the decimal?

  11. Syed M. Jafri January 8, 2025 at 10:16 am - Reply

    Stay blessed. Thank you!

    • PNRao January 11, 2025 at 8:27 am - Reply

      You are welcome!

  12. Kikyo February 7, 2025 at 7:45 am - Reply

    everytime i close the excel the code also gone

    • PNRao February 13, 2025 at 11:43 am - Reply

      If you are using the VBA Code, you must save the Excel File as Macro Enabled File (.xlsm) format.
      Regards
      PNRao!

Leave A Comment