20 August 2011
Function Ntow (amt As Variant) As Variant Dim FIGURE As Variant Dim LENFIG As Integer Dim i As Integer Dim WORDs(19) As String Dim tens(9) As String WORDs(1)= "One" WORDs(2)= "Two" WORDs(3)= "Three" WORDs(4)= "Four" WORDs(5)= "Five" WORDs(6)= "Six" WORDs(7)= "Seven" WORDs(8)= "Eight" WORDs(9)= "Nine" WORDs(10)= "Ten" WORDs(11)= "Eleven" WORDs(12)= "Twelve" WORDs(13)= "Thirteen" WORDs(14)= "Fourteen" WORDs(15)= "Fifteen" WORDs(16)= "Sixteen" WORDs(17)= "Seventeen" WORDs(18)= "Eighteen" WORDs(19)= "Nineteen"
FIGURE = amt FIGURE = Format(FIGURE,"FIXED") FIGLEN = Len(FIGURE)
If figlen 1 Then Ntow= "Rupees " Elseif Val(Left(figure,9)) = 1 Then Ntow = "Rupee " End If
For i = 1 To 3 If Val(Left(figure,2)) 0 Then Ntow = Ntow & words(Val(Left(figure,2))) Elseif Val(Left(figure,2)) > 19 Then Ntow = Ntow & tens(Val(Left(figure,1))) Ntow = Ntow & words(Val( Right(Left(figure,2),1) )) End If
If i = 1 And Val(Left(figure,2)) > 0 Then Ntow = Ntow & " Crore " Elseif i = 2 And Val(Left(figure,2)) > 0 Then Ntow = Ntow & " Lakh " Elseif i = 3 And Val(Left(figure,2)) > 0 Then Ntow = Ntow & " Thousand " End If figure = Mid(figure,3) Next i
If Val(Left(figure,1)) > 0 Then Ntow = Ntow & words(Val(Left(figure,1))) + " Hundred " End If
figure = Mid(figure,2)
If Val(Left(figure,2)) 0 Then Ntow = Ntow & words(Val(Left(figure,2))) Elseif Val(Left(figure,2)) > 19 Then Ntow = Ntow & tens(Val(Left(figure,1))) Ntow = Ntow & words(Val( Right(Left(figure,2),1) )) End If figure = Mid(figure,4)
If Val(figure) > 0 Then Ntow = Ntow & " Paise " If Val(Left(figure,2)) 0 Then Ntow = Ntow & words(Val(Left(figure,2))) Elseif Val(Left(figure,2)) > 19 Then Ntow = Ntow & tens(Val(Left(figure,1))) Ntow = Ntow & words(Val( Right(Left(figure,2),1) )) End If End If FIGURE = amt FIGURE = Format(FIGURE,"FIXED") If Val(figure) > 0 Then Ntow = Ntow & " Only " End If End Function
20 August 2011
Do it with easy 5 steps , by using this script you can convert number in to indian Rupees.
1)Open excel then Press Alt+F11 to start visual basic editor 2)In visulal basic editor click insert menu then open module 3)copy the script given above and paste in opened module window, on the left side pane click on This work book 4)Press Alt+Q to save the file.
Now save the excel workbook on your hard disk. For MSoffice 2007 You need to select Save as type = Excel Macro enabled Work book. In excel 2003 you can directly save as xls file. You have now created a new excel function called ntow.
In excel 2007 you need to enable macros before typing function.
You can also use this function as =ntow(1500 ) the number will be converted in to words in the same cell.
CAclubindia
