ExcelInsider Team
Representing financial documents, receipts, and cheques requires correctly understanding the Rupee format. Often, we need to convert numerical values to words to present the documents properly and avoid miscommunication. As Excel does not have prebuilt functions, we remain confused about how to convert a number to words in Rupees. However, if you know the easy steps, it is just a piece of cake.
To convert a number to words in Rupees, you can follow these simple steps.
➤ Go to the Formulas Tab and click on Name Manager.
➤ Click on New and add a new custom name.
➤ Paste the complete formula of the LAMBDA function
➤ Click OK and close the window.
➤ Use the custom name as the function and write the cell number inside the brackets.
➤ Press Enter to get the results.
This article will discuss the customized functions and formulas along with the VBA code. With the proper instructions, we will walk you through each method’s easy hacks and limitations. Once you know this, converting numbers to words without a defined function will not be as scary as you think.
Converting Number to Words in Rupees with LAMBDA Function
The LAMBDA function allows you to create customized functions that behave in the same way as built-in functions. Without using any code or macros, you can use the repetitive functions and formulas to do the number-to-word conversion. No need to use any macros or codes.
Steps:
➤ Go to the Formula Tab and click on Name Manager.
➤ Click on New to customize a new function as per requirement.
➤ In the Name, give your customized name resembling what you want.
➤ Beside the Refer to box below the window, paste the following code-
=LAMBDA(n, LET( units, {“”,”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”}, num, INT(n), paise, ROUND((n – INT(n)) * 100, 0), ConvertTwo, LAMBDA(x, IF(x<10, INDEX(units, x+1), IF(x<20, INDEX(teens, x-9), INDEX(tens, INT(x/10)+1) & IF(MOD(x,10)>0, ” ” & INDEX(units, MOD(x,10)+1), “”) ) ) ), ConvertThree, LAMBDA(x, IF(x=0, “”, IF(x<100, ConvertTwo(x), INDEX(units, INT(x/100)+1) & ” Hundred” & IF(MOD(x,100)>0, ” ” & ConvertTwo(MOD(x,100)), “”) ) ) ), words, IF(num=0, “Zero”, TEXTJOIN(” “, TRUE, IF(INT(num/10000000)>0, ConvertTwo(INT(num/10000000)) & ” Crore”, “”), IF(MOD(INT(num/100000),100)>0, ConvertTwo(INT(MOD(num,10000000)/100000)) & ” Lakh”, “”), IF(MOD(INT(num/1000),100)>0, ConvertTwo(INT(MOD(num,100000)/1000)) & ” Thousand”, “”), IF(MOD(INT(num/100),10)>0, INDEX(units, INT(MOD(num,1000)/100)+1) & ” Hundred”, “”), IF(MOD(num,100)>0, ConvertTwo(MOD(num,100)), “”) ) ), result, “Rupees ” & words & IF(paise>0, ” and ” & ConvertTwo(paise) & ” Paise”, “”) & ” Only”, result ))
➤ Press OK, and the Name Manager window will display the new function you created with the name and formula.
➤ Close the window and write the function in the output cell in the following manner –
=Amount_in_Words(C2),
where Amount_in_Words is the customized function name, and the C2 is the cell holding the numerical value.
➤ Drag the cells to fill the rest of the column.
Note:
The Lambda Function is only applicable in Microsoft 365.
Converting Number to Words in Rupees with VBA Macro
VBA Macro is one of the most efficient methods for converting numbers to words in Rupees. This method can dynamically convert any number (including lakhs, crores) to words in the Indian numbering system. Converting the column in a few clicks makes it scalable, especially for business invoices and financial receipts.
Steps:
➤ Go to the Developer tab -> Visual Basic to launch the VBA window.
➤ Click on the Insert tab and choose Module from the dropdown menu.
➤ Paste the below code in the blank window-
Function RupeesInWords(ByVal MyNumber As Double) As String
   Dim wholePart As Long
   Dim paisePart As Integer
   Dim result As String
   wholePart = Int(MyNumber)
   paisePart = Round((MyNumber - wholePart) * 100)
   result = "Rupees " & ConvertToWords(wholePart)
   If paisePart > 0 Then
       result = result & " and " & ConvertToWords(paisePart) & " Paise"
   End If
   RupeesInWords = result & " Only"
End Function
Private Function ConvertToWords(ByVal num As Long) As String
   Dim ones As Variant, tens As Variant
   Dim output As String
   ones = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", _
                "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
   tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
   If num = 0 Then
       ConvertToWords = "Zero"
       Exit Function
   End If
   output = ""
   If num \ 10000000 > 0 Then
       output = output & ConvertToWords(num \ 10000000) & " Crore "
       num = num Mod 10000000
   End If
   If num \ 100000 > 0 Then
       output = output & ConvertToWords(num \ 100000) & " Lakh "
       num = num Mod 100000
   End If
   If num \ 1000 > 0 Then
       output = output & ConvertToWords(num \ 1000) & " Thousand "
       num = num Mod 1000
   End If
   If num \ 100 > 0 Then
       output = output & ConvertToWords(num \ 100) & " Hundred "
       num = num Mod 100
   End If
   If num > 0 Then
       If output <> "" Then output = output & "and "
       If num < 20 Then
           output = output & ones(num)
       Else
           output = output & tens(num \ 10)
           If num Mod 10 > 0 Then
               output = output & "-" & ones(num Mod 10)
           End If
       End If
   End If
   ConvertToWords = Application.WorksheetFunction.Trim(output)
End Function
➤ Save the file and click YES to save it as a macro-free workbook.
➤ Close the VBA window and write the function name as the formula in the desired cell.
=RupeeInWords(C2),
where the name of the formula comes from the VBA code function name, and the C2 is the cell holding Rupee values in numeric form.
➤ Drag the cells to fill the column with the same formula
Note:
This supports numeric values with decimal points up to two.
Frequently Asked Questions
How do you format cells as Rupees in Excel?
Open the Format Cells window by clicking Ctrl+1. In the window, choose Category of Currency. By default, the symbol is selected as the Dollar sign. Change it to Rupee to get the desired result.
How do you convert numbers to Rupees if VBA is not enabled?
You can use the LAMBDA function in Microsoft 365 if VBA Macro is not enabled. App Script can also be a good alternative if you use Google Sheets.
How do you convert a decimal to paisa in Excel?
As you know, one hundred paisa equals one Rupee, and converting decimals requires this conversion method. Multiply the decimal values by 100 to change them to paisa. The LAMBDA or VBA code should be tailored to handle this function.
Why does VBA not work over crore?
VBA does not have any limitations and can work with values above crores. However, the code can have issues with data type and performance. This, in turn, can make VBA unsuitable for values exceeding crores.
Why can my Name Manager hold longer logic?
Name Manager has a limit of characters above 8000. To store a longer formula, use the LAMBDA function. However, the more preferred approach is VBA Macro.
Can I use these methods in Google Sheets?
Google Sheets does not support these methods. While working with Sheets, you can use the same formulas and logic in Google Apps Scripts instead.
Concluding Words
The dilemma of how to convert a number to words in Excel in Rupees exists due to a lack of a natively built formula for this. However, there are methods like VBA and formula-based ones that you tailor according to your needs. Now that you know the available options, you can easily identify which methods to use with large datasets and in restricted environments.
If you find this guide helpful, please share your thoughts and feedback with us. Don’t just rely on the pictures; download the Excel workbooks for a quick start.
