Rupees in words - Excel
- Excel - Alt+F11
- Insert - Module
- Paste below codes and save as macro enable excel format
- Use the formula “convert english”.
- Excel - Alt+F11
- Insert - Module
- Paste below codes and save as macro enable excel format
- Use the formula “convert english”.
1. Function ConvertCurrencyToEnglish(ByVal MyNumber)
2. ' Edited by Karthikeyan
karthikeyan@livetolearn.in
3. Dim Temp
4. Dim Rupees, Paise
5. Dim DecimalPlace, Count
6.
7. ReDim Place(9) As String
8. Place(2) = " Thousand "
9. Place(3) = " lakh "
10. Place(4) = " Crore "
11.
12.
13. ' Convert MyNumber to a string, trimming extra
spaces.
14. MyNumber = Trim(Str(MyNumber))
15.
16. ' Find decimal place.
17. DecimalPlace = InStr(MyNumber, ".")
18.
19. ' If we find decimal place...
20. If DecimalPlace > 0 Then
21. ' Convert Paise
22. Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
23. ' Hi! Note the above line Mid function it gives
right portion
24. ' after the decimal point
25. 'if only . and no numbers such as 789. accures, mid
returns nothing
26. ' to avoid error we added 00
27. ' Left function gives only left portion of the
string with specified places here 2
28.
29.
30. Paise = ConvertTens(Temp)
31.
32.
33. ' Strip off paise from remainder to convert.
34. MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
35. End If
36.
37. Count = 1
38. If MyNumber <> "" Then
39.
40. ' Convert last 3 digits of MyNumber to Indian
Rupees.
41. Temp = ConvertHundreds(Right(MyNumber, 3))
42.
43. If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
44.
45. If Len(MyNumber) > 3 Then
46. ' Remove last 3 converted digits from
MyNumber.
47. MyNumber = Left(MyNumber, Len(MyNumber) - 3)
48. Else
49. MyNumber = ""
50. End If
51.
52. End If
53.
54. ' convert last two digits to of mynumber
55. Count = 2
56.
57. Do While MyNumber <> ""
58. Temp = ConvertTens(Right("0" & MyNumber, 2))
59.
60. If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
61. If Len(MyNumber) > 2 Then
62. ' Remove last 2 converted digits from
MyNumber.
63. MyNumber = Left(MyNumber, Len(MyNumber) - 2)
64.
65. Else
66. MyNumber = ""
67. End If
68. Count = Count + 1
69.
70. Loop
71.
72.
73.
74.
75. ' Clean up rupees.
76. Select Case Rupees
77. Case ""
78. Rupees = ""
79. Case "One"
80. Rupees = "Rupee One"
81. Case Else
82. Rupees = "Rupees " & Rupees
83. End Select
84.
85. ' Clean up paise.
86. Select Case Paise
87. Case ""
88. Paise = ""
89. Case "One"
90. Paise = "One Paise"
91. Case Else
92. Paise = Paise & " Paise"
93. End Select
94.
95. If Rupees = "" Then
96. ConvertCurrencyToEnglish = Paise &
" Only"
97. ElseIf Paise = "" Then
98. ConvertCurrencyToEnglish = Rupees
& "
Only"
99. Else
100.
ConvertCurrencyToEnglish
= Rupees & "
and " & Paise & " Only"
101.
End If
102.
103.
End Function
104.
105.
106.
Private Function ConvertDigit(ByVal MyDigit)
107.
Select Case Val(MyDigit)
108.
Case 1: ConvertDigit = "One"
109.
Case 2: ConvertDigit = "Two"
110.
Case 3: ConvertDigit = "Three"
111.
Case 4: ConvertDigit = "Four"
112.
Case 5: ConvertDigit = "Five"
113.
Case 6: ConvertDigit = "Six"
114.
Case 7: ConvertDigit = "Seven"
115.
Case 8: ConvertDigit = "Eight"
116.
Case 9: ConvertDigit = "Nine"
117.
Case Else: ConvertDigit = ""
118.
End Select
119.
120.
End Function
121.
122.
Private Function ConvertHundreds(ByVal MyNumber)
123.
Dim Result As String
124.
125.
' Exit if there is nothing to convert.
126.
If Val(MyNumber) = 0 Then Exit Function
127.
128.
' Append leading zeros to number.
129.
MyNumber = Right("000" & MyNumber, 3)
130.
131.
' Do we have a hundreds place digit to
convert?
132.
If Left(MyNumber, 1) <> "0" Then
133.
Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
134.
End If
135.
136.
' Do we have a tens place digit to
convert?
137.
If Mid(MyNumber, 2, 1) <> "0" Then
138.
Result = Result &
ConvertTens(Mid(MyNumber, 2))
139.
Else
140.
' If not, then convert the ones place
digit.
141.
Result = Result &
ConvertDigit(Mid(MyNumber, 3))
142.
End If
143.
144.
ConvertHundreds = Trim(Result)
145.
End Function
146.
147.
148.
Private Function ConvertTens(ByVal MyTens)
149.
Dim Result As String
150.
151.
' Is value between 10 and 19?
152.
If Val(Left(MyTens, 1)) = 1 Then
153.
Select Case Val(MyTens)
154.
Case 10: Result = "Ten"
155.
Case 11: Result = "Eleven"
156.
Case 12: Result = "Twelve"
157.
Case 13: Result = "Thirteen"
158.
Case 14: Result = "Fourteen"
159.
Case 15: Result = "Fifteen"
160.
Case 16: Result = "Sixteen"
161.
Case 17: Result = "Seventeen"
162.
Case 18: Result = "Eighteen"
163.
Case 19: Result = "Nineteen"
164.
Case Else
165.
End Select
166.
Else
167.
' .. otherwise it's between 20 and 99.
168.
Select Case Val(Left(MyTens, 1))
169.
Case 2: Result = "Twenty "
170.
Case 3: Result = "Thirty "
171.
Case 4: Result = "Forty "
172.
Case 5: Result = "Fifty "
173.
Case 6: Result = "Sixty "
174.
Case 7: Result = "Seventy "
175.
Case 8: Result = "Eighty "
176.
Case 9: Result = "Ninety "
177.
Case Else
178.
End Select
179.
180.
' Convert ones place digit.
181.
Result = Result &
ConvertDigit(Right(MyTens, 1))
182.
End If
183.
184.
ConvertTens = Result
185.
End Function
No comments:
Post a Comment