Excel User Define Function/C Language/Syllabus Update of PSEB/ Learning Computer
Excel User Define Function,Common Used Functions, C Language Some Good Questions, Syllabus of Pseb, Learn Computer Basic Step by Step.
Tuesday, 25 September 2012
Convert Number into Indian Rupees Word
2. On the Code group click Visual Basic .
Copy and paste the below NumbertoRupee Function in to Module1
Function NumbertoRupee(ByVal MyNumber, Optional incRupees As Boolean = True)
Dim Crores, Lakhs, Rupees, Paise, Temp
Dim DecimalPlace As Long, Count As Long
Dim myLakhs, myCrores
ReDim Place(9) As String
Place(2) = " Thousand ": Place(3) = " Million "
Place(4) = " Billion ": Place(5) = " Trillion "
' String representation of amount.
' Position of decimal place 0 if none.
' Convert Paise and set MyNumber to Rupees amount.
If DecimalPlace > 0 Then
Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
myCrores = MyNumber \ 10000000
myLakhs = (MyNumber - myCrores * 10000000) \ 100000
MyNumber = MyNumber - myCrores * 10000000 - myLakhs * 100000
Temp = GetHundreds(Right(myCrores, 3))
If Temp <> "" Then Crores = Temp & Place(Count) & Crores
If Len(myCrores) > 3 Then
myCrores = Left(myCrores, Len(myCrores) - 3)
Count = Count + 1
Temp = GetHundreds(Right(myLakhs, 3))
If Temp <> "" Then Lakhs = Temp & Place(Count) & Lakhs
If Len(myLakhs) > 3 Then
myLakhs = Left(myLakhs, Len(myLakhs) - 3)
Count = Count + 1
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Count = Count + 1
Select Case Crores
Case "One": Crores = " One Crore "
Case Else: Crores = Crores & " Crores "
Select Case Lakhs
Case "One": Lakhs = " One Lakh "
Case Else: Lakhs = Lakhs & " Lakhs "
Select Case Rupees
Case "": Rupees = "Zero "
Case "One": Rupees = "One "
Select Case Paise
Case "": Paise = " and Paise Zero Only "
Case "One": Paise = " and Paise One Only "
Case Else: Paise = " and Paise " & Paise & " Only "
SpellNumber = IIf(incRupees, "Rupees ", "") & Crores & _
Lakhs & Rupees & Paise
' 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 "
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Result = Result & GetDigit(Mid(MyNumber, 3))
' Converts a number from 10 to 99 into text.
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"
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 "
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
' Converts a number from 1 to 9 into text.
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 = ""
7 comments:
User can also try free GWORD addin formula which auto convert numbers or amount to words or rupees.
Download Link - www.xltool.in | www.gwordformula.blogspot.com
Witch one you copy and past Pls give the details .and video
This macro can only handle amounts up to 2,147,483,647.99. I suppose this is due to size limitations for the Long data type.
Made some changes to support up to 999999999999.99 (Excel limitation)
Dim Crores As String, Lakhs As String, Rupees As String, Paise As String, Temp As String
Dim DecimalPlace As Long, Count As Long
Dim myLakhs As String, myCrores As String
Dim Result As String
Dim myDecNumber As Variant
ReDim Place(9) As String
If DecimalPlace > 0 Then
Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
On Error Resume Next
myCrores = Fix(myDecNumber / 10000000)
myLakhs = Fix((myDecNumber - myCrores * 10000000) / 100000)
MyNumber = CStr(myDecNumber - myCrores * 10000000 - myLakhs * 100000)
On Error GoTo 0
Temp = GetHundreds(Right(myCrores, 3))
If Temp <> "" Then Crores = Temp & Place(Count) & Crores
If Len(myCrores) > 3 Then
myCrores = Left(myCrores, Len(myCrores) - 3)
Count = Count + 1
Temp = GetHundreds(Right(myLakhs, 3))
If Temp <> "" Then Lakhs = Temp & Place(Count) & Lakhs
If Len(myLakhs) > 3 Then
myLakhs = Left(myLakhs, Len(myLakhs) - 3)
Count = Count + 1
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Count = Count + 1
Select Case Crores
Case Else: Crores = Crores & "Crores "
Select Case Lakhs
Case Else: Lakhs = Lakhs & "Lakhs "
Select Case Rupees
Case Else: Rupees = Rupees
Select Case Paise
Case Else: Paise = "Paise " & Paise
This Code Doesn't work… Could u please suggest what to do..
Free Excel Add-in Convert Excel cell Numbers to Words with Prefix and Suffix features.