What we needed was to show a number in the Indian Rupee format as well as display the amount in words. Let me explain. Say we have a figure 12345678. If I just use the currency format it will be displayed as Rs 12,345,678.00. But in India we prefer to use the format Rs. 1,23,45,678.00. That is we use hundreds, thousands, lakhs and crores rather than millions and billions. As far as I know there is no built in method to display it in the Indian format. (I haven’t tried Office 2007 yet.). Before I developed this Add-In we had to do it manually each time up to 50 times a day. This Add-In saved us a lot of time.
The INR() function converts a number to the Indian Style Comma formatted currency as you can see in the snapshot. The commas are placed in the right places separating lakhs and crores. But the result that you get is in the Text Format. So you cannot directly use the result in a formula for calculations. To overcome this issue I have added another function calledREVINR().
The REVINR() function simply converts the result obtained from using INR function back to the number format so that you can use it in calculations as demonstrated in snapshot.
The Add-In has 3 functions – INR(), REVINR() and RSWORDS().
The INR() function converts a number to the Indian Style Comma formatted currency as you can see in the snapshot. The commas are placed in the right places separating lakhs and crores. But the result that you get is in the Text Format. So you cannot directly use the result in a formula for calculations. To overcome this issue I have added another function calledREVINR().The REVINR() function simply converts the result obtained from using INR function back to the number format so that you can use it in calculations as demonstrated in snapshot.
The RSWORDS() function converts any number to currency in the form of words. This can be quite useful if you generate your invoices using Excel and you want the amount to be displayed in words.
How to install SureshAddIns?
Installing AddIns in Excel is very simple. First you download the AddIn from the link at the bottom of this post and save it on your computer.
Open Microsoft Excel. Click on Tools>Add-Ins. Click on Browse and navigate to wherever you saved the SureshAddIns.xla and then click OK. You have installed the AddIn.
How to use SureshAddIns?
You can use SureshAddIns by directly entering the functions into a cell like =INR(XX), =REVINR(XX) and =RSWORDS(XX) where XX is the reference of the cell you want to convert.
Another way is to use the Insert Function window which you can open either through the fx button on the toolbar or though the Insert>Function menu. In the category dropdown box choose User defined and you will see the above three functions of SureshAddIns.
You are free to use the Add-In as you like. Please do let me know your feedback. Also, if you find any difficulty in using it do contact me at suresh[at]winsila[dot]com.
credit for this post goes to http://www.dq.winsila.com and for other tricks visit suresh site http://www.dq.winsila.com
Load or unload an Excel add-in program- Click the Microsoft Office Button
, and then click Excel Options.
- Click the Add-Ins category.
- In the Manage box, click Excel Add-ins, and then click Go.
- To load an Excel add-in, do the following:
- In the Add-Ins available box, select the check box next to the add-in that you want to load, and then click OK.
- If the add-in is not currently installed on your computer, click Yes to install it.
TIP Follow the setup instructions as needed.
- To unload an Excel add-in, do the following:
- In the Add-Ins available box, clear the check box next to the add-in that you want to unload, and then click OK.
- To remove the add-in from the Office Fluent Ribbon, restart Excel.
NOTE Unloading or removing an add-in from the Ribbon does not delete the add-in from your computer. To delete the add-in from your computer, you must uninstall it.
For Excel 2003 and earlier versions
To install an addin for Excel install addin from Excel's menual Tool->Addin... then browse.
Do not change the Place of File after installation.Further If you want to send excel files where this addin has been used then it will not work where addin has not been installed
For Excel 2003 and earlier versions
To install an addin for Excel install addin from Excel's menual Tool->Addin... then browse.
Do not change the Place of File after installation.Further If you want to send excel files where this addin has been used then it will not work where addin has not been installed
Truly it is very useful for excel user in india.Thanks a lot to Creator.
ReplyDeleteThanks,
Harendra Kumar
Pune, India.
Sir,
ReplyDeleteThis function is good but one problem is rise i.e. if I add Rs. in figure than other link columns & row found error i.e. they do not calculate total, percantage or etc.
dear HArdbdra,
ReplyDeleteamount in figures and words are normally used in separate cell. so for calculation use amount in figure
great work... thanks a lot....... Keep up....
ReplyDeletethanks it is Truly very useful for excel user in india.Thanks a lot to Creator.
ReplyDeleteIndia
Thanks !
ReplyDeletesatish_asian2000@yahoo.com
I didn't find the download link
ReplyDeletehere is the link
Deletehttp://www.dq.winsila.com/wp-content/uploads/2007/03/SureshAddin.xla
thanks a lot...... super like
ReplyDeleteDear sir it will not give the result in words if the figures are in negative. please do the same in negative case and give updated version of this.
ReplyDeletethanks
kalpesh shah
9890208175 / 8767131113
kalpeshyshah@rediffmail.com
Sir, Please change Rs. sign to New Indian Rupee symbol when wirting in words
ReplyDeleteHey........ friend this is not working in excel 2007.Please give me the function which can convert the currency in INR in excel 2007, pleeeeeese.
ReplyDeleteIt is also working in Microsoft excel 2007 ,Kindly follow the instruction given above Sr no 1-5 carefully
DeleteIt will work, simply change its extension from .xls to .xla
Deletevnsukla@yahoo.com
Its a fantastic....
ReplyDeleteFinally its done.. Thanks for the link, great job! Keep it up
ReplyDeleteGreat.......its a very usful thanks a lot
ReplyDeleteI am having two properties, one is self occupied, and other is let out.
ReplyDeletemy annual income is below 5 lacs, and interest from saving is also below Rs. 10,000.
I have availed home loan on both the houses, and want to claim interest and principal paid.
1) whether I have to file return for f.y. 2011-12 and a.y 2012-13.
2) if yes, which return Itr-1 or itr-2
3) where to claim lossess of house property
4) is it in sr. no. 6 of part-B of TI computation of Income of ITR-2.
and sch-CYLA by deducting the loss from salary.
5) where to claim principal paid for emi for the year.
please reply. thanks
Point wise answer is given below.
Delete1.You have to file return for Fy 2011-12
2.ITR-2
3.You have to fill sch HP house property read how to fill here
http://www.simpletaxindia.net/2012/07/house-property-income-tax-return-filing.html
4. Fill ITR form in excel sheet figure will be filled automatically
5. principal is covered u/s 80C sch VIA in itr-2
thanks for quick reply.
DeleteDownload Free Utility to Convert Excel Number to Word / Text / Rupees.
ReplyDeleteSOURCE -
http://www.soft.nkworld.in/2011/05/download-ms-excel-hidden-menu-and.html
Hi suresh,
ReplyDeletei dont want to show the paise thing in the figures ,i just want round of figures
how is is possible?
here is a small macro. Just paste it and use it:
DeletePublic Function f2w(num As Long) As String
Dim intpart As Long, decpart As Integer, park As Integer, zastring As String
zastring = ""
intpart = num
If intpart = 0 Then
zastring = "Rupees nil "
ElseIf intpart < 2 Then
zastring = "Rupee "
Else
zastring = "Rupees "
End If
park = Fix(intpart / 10000000)
zastring = zastring & getword(park, "crore ")
intpart = intpart Mod 10000000
park = Fix(intpart / 100000)
zastring = zastring & getword(park, "lakh ")
intpart = intpart Mod 100000
park = Fix(intpart / 1000)
zastring = zastring & getword(park, "thousand ")
intpart = intpart Mod 1000
park = Fix(intpart / 100)
zastring = zastring & getword(park, "hundred ")
park = intpart Mod 100
zastring = zastring & getword(park, " only")
f2w = zastring
End Function
Function getword(t3 As Integer, plas As String) As String
If t3 = 0 Then
getword = ""
ElseIf t3 < 20 Then
getword = retteen(t3) & " " & plas
Else
getword = retten(Fix(t3 / 10)) & " " & retteen(t3 Mod 10) & plas
End If
End Function
Function retteen(t2 As Integer)
Dim teenstr As String
teenstr = "one two three four five six seven eight nine ten eleven twelve thirteen fourteen fifteen sixteen seventeeneighteen nineteen "
If t2 = 0 Then
retteen = ""
Else
retteen = Trim(Mid(teenstr, (((t2 - 1) * 9) + 1), 9))
End If
End Function
Function retten(t1 As Integer)
Dim tenstr As String
tenstr = "twenty thirty forty fifty sixty seventyeighty ninety "
retten = Trim(Mid(tenstr, (((t1 - 2) * 7) + 1), 7))
End Function
Please tell how to use this
DeleteGreat Boss. !!!!
ReplyDeleteThanks a lot. It is very helpful
ReplyDeleteTHIS HELPS ME TO WRITE NUMBER INTO WORD FORMAT I AM VERY THANK FUL TO YOU
ReplyDeletevery nice to see this feature simplified as add ins Thanks a lot...keep post something useful to every one aware of it.
ReplyDeleteThank you
ReplyDeleteThanks A Zillion Mr. Suresh
ReplyDelete- Nikhil Neelakant
Working Fine Thanks alot
ReplyDeleteThank you, it is very useful.
ReplyDeleteDear Sir,
ReplyDeleteIs it possible to remove "Rs" from starting so that value can be taken as " 300,000.00" if yes plz let me know at
Good One
ReplyDeleteI AM NOT ABLE TO USE.....
ReplyDeletePLEASE HELP
I HAVE EXCEL 2010
sir
ReplyDeletewhen i transfered this file to another pc where this addin is not installed then this is not running
it is already being mentioned in limitation given above. However in 2007 and above you can save file with macro's which is saved with ext. xlsm .if you save file with macro then it will work in other computer also.
DeleteDear Sir,
ReplyDeleteThe function to covert courtesy amount (in figures) to legal amount (in words) is indeed good work.
However, for formatting of amount in figures as per indian practice of lacks and crores is simply provided in OS itself since Windows 98. Go to Control Panel, Regional & Language Options and there customize the numbering formats (under number groupings) which is self explanatory. This is one time step and then no need to covert or re-convert, and it affects display of all the files.
Regards,
Manoj Agarwal - 9937041788
Dear sir,
ReplyDeletePlease help me i m not sucess this task
Rohit
cool................ very very nice
ReplyDeleteVery good,
ReplyDeleteKindly give the suggestion to remove Rupees text from words
hello all,
ReplyDeletewhen i click on the download link -
http://www.dq.winsila.com/wp-content/uploads/2007/03/SureshAddin.xla
it will open a new page with the zunk character.
unable to download, please help
but its working well when we have click ,so pls provide your mail address
Deleteim using firefox, thats why face the problem, i tried using chrome and IE, both working fine. thnx for such a nice add-on. :)
DeleteHey This is Amazing is this feature also available in MS Word ? what am I supposed to do if I want this to work in MS Word ?
ReplyDeleteThanks
Thank you, it is very useful.
ReplyDeleteThank you ...its great and very useful..
ReplyDeleteThanks a Lot... It is Very Helpful...............
ReplyDeletegreat work... thanks a lot....... Keep up.... Please prepare Under rupees -------------------------- only ) by enhancing on number. It will be used to place protective words in preparing bills
ReplyDeleteZillion Thanks ....
ReplyDeleteThanks a lot!
ReplyDeletevery helpfull.....many many thanks....but ask u again same q....remove "Rs" from starting...?? i need only words ....please help
ReplyDeletevery helpfull.....many many thanks....but ask u again same q....remove "Rs" from starting...?? i need only words ....please help
ReplyDeletevery helpful... thanks a lot...
ReplyDeleteafter instulation,how to use this formula
ReplyDelete