Formula

This query is : Open

Rahul Sharma (Querist)
Follow

17 August 2011 Sir, What is the exact formula of Number convert to text?
for example

total sum of Rs.1,000/- i want total sum in
Text Format (Rupees One thousand only)

Thanks in advance
Rahul Sharma

CMA. CS. Sanjay Gupta

CMA. CS. Sanjay Gupta (Expert)
Follow

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"

tens(2) = "Twenty"
tens(3) = "Thirty"
tens(4) = "Fourty"
tens(5) = "Fifty"
tens(6) = "Sixty"
tens(7) = "Seventy"
tens(8) = "Eighty"
tens(9) = "Ninety"

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

CMA. CS. Sanjay Gupta

CMA. CS. Sanjay Gupta (Expert)
Follow

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.

Upgrade to CAclubindia Pro. Subscribe Now!

CA Siddhartha Bhardwaj

CA Siddhartha Bhardwaj (Expert)
Follow

10 October 2011 Sir,

This code is giving a syntax error at "If figlen 1 Then" when I enter the formula.

Kindly help.

Can you mail me the excel sheet with this code?

My email id is sidcoolguy007@gmail.com

Thanks in advance.

CA Siddhartha Bhardwaj

CA Siddhartha Bhardwaj (Expert)
Follow