How to Convert Numbers into Rupees in Words in MS Excel? When working with financial documents like invoices, pay slips, or reports in MS Excel, you might need to express numbers such as 123456.78 in Indian Rupees in words (for example, Rupees One Lakh Twenty Three Thousand Four Hundred Fifty Six and Seventy Eight Paise Only). This is a common and crucial need in Indian accounting and financial reporting for enhancing clarity and transparency in transactions.
MS Excel offers powerful tools for number formatting, calculations, and data analysis but lacks a native function to convert numbers into words using the Indian numbering system (which includes specific terminology like Thousand, Lakh, Crore for denoting large amounts). Fortunately, here are several effective methods lets explore together. In this article I will show you How to Convert Numbers into Rupees in Words in MS Excel? lets start.
Microsoft Excel does not have a direct option or formula to convert numbers to words in Rupees. For this, you need to use a small code in VBA (Visual Basic for Applications).
How to apply VBA Code in MS Excel?
Here are the steps to Apply VBA Code in MS Excel:
Step 1: Open MS Excel then Press Alt + F11 on your keyboard.
Step 2: Click on Insert and then select Module.
Step 3: In the module, you can paste a VBA code that converts numbers to words in Rupees.
Step 4: Save the file by giving any name, then you need to select MS Excel Macro-Enabled Workbook. This is important because it saves the VBA code and allows you to use it later.
After saving the file, you can close it.
Code:
Function NumToRupeesWords(ByVal n)
Dim u, t, i As Integer, s$, x$, p$
u = Split(“Zero One Two Three Four Five Six Seven Eight Nine Ten Eleven Twelve Thirteen Fourteen Fifteen Sixteen Seventeen Eighteen Nineteen Twenty Thirty Forty Fifty Sixty Seventy Eighty Ninety”)
t = Split(“Crore,Lakh,Thousand,Hundred”, “,”)
x = Format(Int(n), “000000000”)
p = Format((n – Int(n)) * 100, “00”)
If Val(Mid(x, 1, 2)) > 0 Then s = s & Word(Val(Mid(x, 1, 2)), u) & ” ” & t(0) & ” “
If Val(Mid(x, 3, 2)) > 0 Then s = s & Word(Val(Mid(x, 3, 2)), u) & ” ” & t(1) & ” “
If Val(Mid(x, 5, 2)) > 0 Then s = s & Word(Val(Mid(x, 5, 2)), u) & ” ” & t(2) & ” “
If Val(Mid(x, 7, 1)) > 0 Then s = s & Word(Val(Mid(x, 7, 1)), u) & ” ” & t(3) & ” “
If Val(Mid(x, 8, 2)) > 0 Then s = s & Word(Val(Mid(x, 8, 2)), u)
s = Application.WorksheetFunction.Proper(Trim(s))
If s = “” Then s = “Zero”
If p <> “00” Then
NumToRupeesWords = “Rupees ” & s & ” and ” & Word(Val(p), u) & ” Paise Only”
Else
NumToRupeesWords = “Rupees ” & s & ” Only”
End If
End Function
Function Word(n, u)
Dim w$
If n = 0 Then Exit Function
If n < 21 Then
w = u(n)
Else
w = u(18 + n \ 10)
If n Mod 10 > 0 Then w = w & ” ” & u(n Mod 10)
End If
Word = w
End Function
How to Convert Numbers into Rupees in Words in MS Excel?
Here are the steps to Convert Numbers to Rupees in Words in MS Excel.
Step 1: Open Excel, then Press Alt + F11 to open the VBA editor, Insert a Module, then write or Paste VBA Code
Step 2: After adding the VBA code write some numbers in your excel sheet’s column, and then write =NumberToRupees on the other column, then select the number you want to convert to words in Rupees, and close the bracket and then press Enter.
if you want to convert more than one number then select the cell where you’ve used the function then press Control + D. This will apply the function to other numbers and convert them to words in Rupees.
The Result
This is the way to convert numbers into Rupees in Words in MS Excel.
Why Converting Numbers to Indian Rupees in Words in MS Excel?
Converting numbers to words in Excel, particularly in the context of Indian rupees, serves several important purposes. Here are some reasons:
- Converting numbers to words in financial documents like invoices, receipts, or cheques adds clarity and reduces ambiguity about the amount being referred to.
- Expressing amounts in both numbers and words helps prevent misinterpretation or fraud by making it harder to alter amounts without detection.
- In some financial or legal contexts, expressing amounts in words is a requirement for documentation.
- For users who prefer or need to understand amounts in a textual format, converting numbers to words enhances readability and comprehension.
- In automated reporting or document generation, having a function to convert numbers to words can streamline the process and ensure consistency.
How do I ensure the VBA code for converting numbers to rupees in words handles negative numbers correctly?
To ensure the VBA code handles negative numbers correctly, you can modify the code to check if the input number is negative and then prepend Minus or Negative to the words representing the absolute value of the number. \
or example, -123 would be converted to Minus Rupees One Hundred Twenty Three Only. In the VBA code, you’d typically check the sign of the number at the beginning of the function and adjust the output accordingly. This way, the function can handle both positive and negative numbers accurately.
Is it possible to convert numbers to words in multiple currencies (not just INR) using a similar method in Excel?
Yes, it’s possible to adapt the method for converting numbers to words in multiple currencies using a similar approach in Excel. You would need to adjust the VBA code to handle the specific currency’s numbering system and currency names.
For example, for US dollars, you’d express numbers in words using Dollars instead of Rupees and handle the currency’s specifics like cents instead of paise. The logic of converting numbers to words remains similar, but the currency-specific terms and maybe decimal handling (like cents for USD) would need adjustments in the code.
Can I use Excel formulas instead of VBA to convert numbers to rupees in words?
Using Excel formulas alone to convert numbers to rupees in words is quite challenging without VBA because Excel’s built-in text functions aren’t designed for converting numbers into full words, especially considering the complexity of the Indian numbering system. While you might achieve simple conversions with combinations of formulas for very limited cases, for a robust solution that handles a wide range of numbers and formats correctly in Indian Rupees, VBA is more practical and commonly used.
Are there any limitations to using VBA for converting numbers to rupees in words in Excel?
Using VBA for converting numbers to rupees in words in Excel is a powerful method but has some limitations. Macros need to be enabled for the code to run, which might be restricted in some environments due to security settings. Also, VBA code might need adjustments for different versions of Excel or if Excel’s VBA support changes. Additionally, debugging and maintaining VBA code requires some programming knowledge. Despite these, VBA is a flexible and effective way to achieve this conversion in Excel.
Conclusion:
Converting numbers to rupees in words in MS Excel is a useful feature for financial documents, invoices, and reports, especially in the Indian context where expressing amounts in both numbers and words is a common practice. This can be efficiently achieved using VBA (Visual Basic for Applications) in Excel.
By creating a custom VBA function, you can convert numerical amounts into their word equivalents in rupees, handling terms like lakhs and crores specific to the Indian numbering system. This method allows for flexibility and accuracy in representing financial amounts in a more readable and understandable format.
In summary, utilizing VBA in Excel for converting numbers to rupees in words is a practical solution that enhances the presentation of financial data. It involves writing or using a VBA code that can handle the nuances of the Indian currency system, including rupees and paise. Once implemented, this function can be easily used across your Excel sheets like any other formula.
Whether you’re dealing with large amounts in crores or smaller figures, this approach ensures that numbers are accurately represented in words, aiding in clarity and reducing potential confusion in financial documentation.