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.

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

You need to be the querist or approved CAclub expert to take part in this query .
Click here to login now

Join the CA Students WhatsApp Community for ICAI Updates, Trending Courses & Notes!

Similar Resolved Queries

Unanswered Queries

Popular
Recent

Trending Online Classes

Direct Tax Laws & International Taxation May 26,Sept 26 & Jan 27

CA Arvind Tuli

Enroll Now
Audit Fast Track Recorded Batch For May 26,Sept 26 & Jan 27

CA Aarti Lahoti

Enroll Now
DT & Audit (Exam Oriented Fastrack Batch) - For May 26 Exams and onwards Full English

CA Bhanwar Borana & CA Shubham Keswani

Enroll Now

view all classess

Meet our CAclubindia PRO Members

SUMAN SAHA

Fredrick Samraj

CA.Pinank Arvindbhai Shah

Sunaram Marndi

Narendra

Subscribe Now

Formula

Similar Resolved Queries

Trending Online Classes

Meet our CAclubindia PRO Members

Quick Links

Browse by Category

Latest Job Openings

Senior Executive

Manager- Finance and Compliance

GST CONSULTANT

GST CONSULTANCY

Accountant

CA Final

Article Assistant

Associate CA