SpellNumber Indian Rupees Function is an Excel function manually created with VBA code to change a given number to written words in Indian Rupees. In other words, it converts a numeric value of INR to words along with paise.
Microsoft Excel does not have an inbuilt function to display the numeric value in words. As many and many users demanded Microsoft has provided the SpellNumber VBA code.
It can be inserted into your Excel workbook. This function spells the numeric value to words as the name suggests.
In simple terms, to display $ 2,345.50 as “Dollar Two Thousand Three Hundred Forty-Five and Fifty Cents”, you can use the SpellNumber Function.
Whereas, to display INR 2,345.50 as ” Two Thousand Three Hundred Forty-Five Rupees and Fifty Paise ” using the SpellNumber Indian Rupees Function.
We have edited the same code with some changes for the required result.
In this article, we will discuss the whole step by step process on how to create this SpellNumber Function for Indian Rupees using the VBA.
You can also check our SpellNumber Without Currency and SpellNumber for UAE Dirhams.
How To Create SpellNumber Indian Rupees Function
Step 1: Open a new workbook.
Step 2: In the Top Ribbon, Go To Developer Tab.
Step 3: Click on “Visual Basic” in Code.
Step 4: Click on “Module” under the Insert menu as shown below:
A blank module sheet will open.
Step 4: Copy the below code and paste it into this Module sheet.
Code for SpellNumber Function for INR
| Option Explicit | |
| 'Main Function www.ExcelDataPro.com | |
| Function SpellNumberEDP2(ByVal MyNumber, Optional MyCurrency As String = "") | |
| Dim Dollars, Cents, Temp | |
| Dim DecimalPlace, Count, x | |
| ReDim Place(9) As String | |
| Place(2) = " Thousand " | |
| Place(3) = " Lacs " | |
| Place(4) = " Crores " | |
| Place(5) = " Hundred Crores " | |
| ' String representation of amount. | |
| MyNumber = Trim(Str(MyNumber)) | |
| ' Position of decimal place 0 if none. | |
| DecimalPlace = InStr(MyNumber, ".") | |
| ' Convert cents and set MyNumber to dollar amount. | |
| If DecimalPlace > 0 Then | |
| Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _ | |
| "00", 2)) | |
| MyNumber = Trim(Left(MyNumber, DecimalPlace – 1)) | |
| End If | |
| Count = 1 | |
| Do While MyNumber <> "" | |
| If Count = 1 Then | |
| x = 3 | |
| Else | |
| x = 2 | |
| End If | |
| Temp = GetHundreds(Right(MyNumber, x)) | |
| If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars | |
| If Len(MyNumber) > x Then | |
| MyNumber = Left(MyNumber, Len(MyNumber) – x) | |
| Else | |
| MyNumber = "" | |
| End If | |
| Count = Count + 1 | |
| Loop | |
| Dim str_amount, str_amounts | |
| Dim str_cent, str_cents | |
| Select Case UCase(MyCurrency) | |
| Case "INR" | |
| str_amount = "Rupee" | |
| str_amounts = "Rupees" | |
| str_cent = "Paisa" | |
| str_cents = "Paisas" | |
| Case "PKR" | |
| str_amount = "Rupee" | |
| str_amounts = "Rupees" | |
| str_cent = "Paisa" | |
| str_cents = "Paisas" | |
| Case "BDT" | |
| str_amount = "Taka" | |
| str_amounts = "Takas" | |
| str_cent = "Poysha" | |
| str_cents = "Poysha" | |
| End Select | |
| Select Case Dollars | |
| Case "" | |
| Dollars = "No " & str_amounts | |
| Case "One" | |
| Dollars = "One " & str_amount | |
| Case Else | |
| Dollars = Dollars & " " & str_amounts | |
| End Select | |
| Select Case Cents | |
| Case "" | |
| Cents = " and No " & str_cents | |
| Case "One" | |
| Cents = " and One " & str_cent | |
| Case Else | |
| Cents = " and " & Cents & " " & str_cents | |
| End Select | |
| SpellNumberEDP2 = Dollars & Cents | |
| 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 |
Error Rectification
If the above code doesn’t run or gives the following error “COMPILE ERROR” then follow the following steps:
- Click on “view raw” below the code.
- A new window will open with the code.
- Copy that code and paste it into the VBA module instead of direct copy paste.
Step 5: Click the “Save” button or press Ctrl+S. The screen will display a dialog box as shown below.
Your file now consists of a macro thus you need to click the “No” option.
Step 6: Select “Save As”.
Select the desired location where you want to save this file and select the “Save as type” as an “Excel macro-enabled workbook”.
SpellNumber function for INR (Indian Rupees) is now created and saved in your workbook.
Using SpellNumber Indian Function
Let us try the above code which we created in our workbook.
Insert number in column A. In column B insert the formula: = SpellNumberEDP2(A1, “INR”). Use the fill handle function to copy the formula in all rows.
It will display the word Indian rupees format as shown below:
Note: Please keep in mind that this function will only be available in this workbook. Follow the above spets to get this function in another workbook.
Please keep in mind that every time you open this workbook or any other macro-enabled workbook you will get a security warning below the ribbon. See the image below:
Select “Enable this content” and click on “OK”. Otherwise, the code will not function.
We thank our readers for liking, sharing, and following us on different social media platforms.
If you have any queries or suggestions please share in the comment section below. I will be more than happy to assist you.
