Convert INR Rupees to Words |
您所在的位置：网站首页 › excelf12 › Convert INR Rupees to Words |
Convert INR Rupees to Words
 While making an invoice, receipt, voucher, or cheque template in Microsoft Excel, converting numerical Indian Rupees to words will come in helpful. Today we are going to see how we can convert the same using Visual Basic for Applications famously known as VBA, Particularly without converting the Excel file into Macro-Enabled Workbook. Steps to be followed:Open a New Excel Workbook on your system Navigate to the File tab » Options » Customize Ribbon » Tick (or) enable the Developer Tab Open Visual Basic Editor (or) press Alt + F11 [Navigate to Developer tab » Code group » Visual Basic] Right-click on ThisWorkbook in the left corner » Insert » Module Copy and Paste the VBA Coding given below within a code block, after that close the visual basics editor window Press F12, Name the file as you wish “Convert INR Rupees into Words”, select “Excel Add-in” in save as Type and save the file. Navigate to Excel Add-ins, tick the “convert INR Rupees into words” in Add-ins pop-up window and press OK. Now type an amount in any cell and use the function called “=CONVERT_TO_INR” Note: Since we have added the VBA Macro coding as Add-in, the function will be accessible for all excel workbooks across within the system.    Function CONVERT_TO_INR(ByVal MyNumber)
'**** By Yogi Anand - Oct-2003
'**** 1000 (Thousand) -- 1,00,000 (Lakh) -- 1,00,00,000 (Crore) -- 1,00,00,00,000 (Arab)
'**** (this UDF is based on SpellNumber by Microsoft)
'****************' Main Function *'****************
Dim Rupees, Paise, temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Lakh "
Place(4) = " Crore "
Place(5) = " Arab " ' String representation of amount
MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none
DecimalPlace = InStr(MyNumber, ".")
'Convert Paise and set MyNumber to Rupee amount
If DecimalPlace > 0 Then
Paise = 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 temp = GetHundreds(Right(MyNumber, 3))
If Count > 1 Then temp = GetHundreds(Right(MyNumber, 2))
If temp "" Then Rupees = temp & Place(Count) & Rupees
If Count = 1 And Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
If Count > 1 And Len(MyNumber) > 2 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 2)
Else
MyNumber = ""
End If
End If
Count = Count + 1
Loop
Select Case Rupees
Case ""
Rupees = "No Rupees"
Case "One"
Rupees = "One Rupee"
Case Else
'****************************************************************
'Rupees = Rupees & " Rupees"
Rupees = "Rupees " & Rupees
End Select
Select Case Paise
Case ""
'****************************************************************
'Paise = ""
Paise = " Only"
Case "One"
Paise = "and One Paisa Only"
Case Else
Paise = " and " & Paise & " Paise Only"
End Select
CONVERT_TO_INR = Rupees & Paise
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
     That concludes this article for now folks. I'll be in touch via email again soon. Don't forget to sign up for our newsletter - it's free and can be incredibly beneficial to your work routine! Idea Credit to Havish MadhvapatyLink to his YouTube Video & GitHub PageStudy Material - Microsoft Support Page Download Sample Working File Thanks for reading Minimum Effort!! Subscribe for free to receive new posts and support my work. |
【本文地址】