Monday, June 25, 2012

CONVERT FIGURES IN TO WORD EXCEL ADD IN INDIAN RUPEE


on Monday, June 25, 2012

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 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.
Download SureshAddIn

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
  1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. Click the Add-Ins category.
  2. In the Manage box, click Excel Add-ins, and then click Go.
  3. To load an Excel add-in, do the following:
    1. In the Add-Ins available box, select the check box next to the add-in that you want to load, and then click OK.
  1. If the add-in is not currently installed on your computer, click Yes to install it.
 TIP   Follow the setup instructions as needed.
  1. To unload an Excel add-in, do the following:
  1. In the Add-Ins available box, clear the check box next to the add-in that you want to unload, and then click OK.
  2. 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 

69 comments: Post Yours! Read Comment Policy ▼
PLEASE NOTE:
We have Zero Tolerance to Spam. Chessy Comments and Comments with Links will be deleted immediately upon our review.

  1. Truly it is very useful for excel user in india.Thanks a lot to Creator.

    Thanks,
    Harendra Kumar
    Pune, India.

    ReplyDelete
    Replies
    1. But this is not working in MS Office 2010.
      Regads
      Ambareesh

      Delete
  2. Sir,

    This 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.

    ReplyDelete
    Replies
    1. If u want to use Rs. or new rupees symbol then first download "Rupees Foradian" and install it in the Font. Then in excel you choose your number by selecting the column then open format cell and then click on the currency button and use symbol none and separate ",". Now you choose custom menu and in the custom menu you type on the begin
      "` " along with other text. After than you choose all the column font as Rupees Foradian.

      If you what old "Rs." then you simply choose select column then format cell and then currency and separate"," . Now you choose custom menu and in the custome menu you type on the begin "Rs. " along with other text.

      You use all the formula and it doesn't arise any error.

      Delete
  3. dear HArdbdra,
    amount in figures and words are normally used in separate cell. so for calculation use amount in figure

    ReplyDelete
  4. great work... thanks a lot....... Keep up....

    ReplyDelete
  5. thanks it is Truly very useful for excel user in india.Thanks a lot to Creator.


    India

    ReplyDelete
  6. Thanks !
    satish_asian2000@yahoo.com

    ReplyDelete
  7. I didn't find the download link

    ReplyDelete
    Replies
    1. here is the link

      http://www.dq.winsila.com/wp-content/uploads/2007/03/SureshAddin.xla

      Delete
  8. Dear 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.

    thanks

    kalpesh shah
    9890208175 / 8767131113
    kalpeshyshah@rediffmail.com

    ReplyDelete
  9. Sir, Please change Rs. sign to New Indian Rupee symbol when wirting in words

    ReplyDelete
  10. Hey........ friend this is not working in excel 2007.Please give me the function which can convert the currency in INR in excel 2007, pleeeeeese.

    ReplyDelete
    Replies
    1. It is also working in Microsoft excel 2007 ,Kindly follow the instruction given above Sr no 1-5 carefully

      Delete
    2. It will work, simply change its extension from .xls to .xla

      vnsukla@yahoo.com

      Delete
  11. Finally its done.. Thanks for the link, great job! Keep it up

    ReplyDelete
  12. Great.......its a very usful thanks a lot

    ReplyDelete
    Replies
    1. What is Service Tax Rate to Service Receiver (if Service Receiver is a Company) on Rent a Cab service received from Service provider (if service provider is a Proprietorship firm)

      Delete
  13. I am having two properties, one is self occupied, and other is let out.
    my 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

    ReplyDelete
    Replies
    1. Point wise answer is given below.

      1.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

      Delete
    2. thanks for quick reply.

      Delete
  14. Download Free Utility to Convert Excel Number to Word / Text / Rupees.
    SOURCE -
    http://www.soft.nkworld.in/2011/05/download-ms-excel-hidden-menu-and.html

    ReplyDelete
  15. Hi suresh,

    i dont want to show the paise thing in the figures ,i just want round of figures
    how is is possible?

    ReplyDelete
    Replies
    1. here is a small macro. Just paste it and use it:
      Public 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

      Delete
  16. Thanks a lot. It is very helpful

    ReplyDelete
  17. THIS HELPS ME TO WRITE NUMBER INTO WORD FORMAT I AM VERY THANK FUL TO YOU

    ReplyDelete
  18. very nice to see this feature simplified as add ins Thanks a lot...keep post something useful to every one aware of it.

    ReplyDelete
  19. Thanks A Zillion Mr. Suresh


    - Nikhil Neelakant

    ReplyDelete
  20. Thank you, it is very useful.

    ReplyDelete
  21. Dear Sir,
    Is it possible to remove "Rs" from starting so that value can be taken as " 300,000.00" if yes plz let me know at

    ReplyDelete
  22. I AM NOT ABLE TO USE.....
    PLEASE HELP
    I HAVE EXCEL 2010

    ReplyDelete
  23. sir

    when i transfered this file to another pc where this addin is not installed then this is not running

    ReplyDelete
    Replies
    1. 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.

      Delete
  24. Dear Sir,
    The 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

    ReplyDelete
  25. Dear sir,

    Please help me i m not sucess this task

    Rohit

    ReplyDelete
  26. cool................ very very nice

    ReplyDelete
  27. Very good,

    Kindly give the suggestion to remove Rupees text from words

    ReplyDelete
  28. hello all,
    when 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

    ReplyDelete
    Replies
    1. but its working well when we have click ,so pls provide your mail address

      Delete
    2. im using firefox, thats why face the problem, i tried using chrome and IE, both working fine. thnx for such a nice add-on. :)

      Delete
  29. Hey 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 ?

    Thanks

    ReplyDelete
  30. Thank you, it is very useful.

    ReplyDelete
  31. Thank you ...its great and very useful..

    ReplyDelete
  32. Thanks a Lot... It is Very Helpful...............

    ReplyDelete
  33. great 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

    ReplyDelete
  34. Zillion Thanks ....

    ReplyDelete
  35. very helpfull.....many many thanks....but ask u again same q....remove "Rs" from starting...?? i need only words ....please help

    ReplyDelete
  36. very helpfull.....many many thanks....but ask u again same q....remove "Rs" from starting...?? i need only words ....please help

    ReplyDelete
  37. very helpful... thanks a lot...

    ReplyDelete
  38. after instulation,how to use this formula

    ReplyDelete
  39. Very very usefull and helpfull please keep it

    Ramesh, Attur

    ReplyDelete
  40. Dear Sir,
    Its nice and useful function for us. Thanks a lot for this. I also requested you to please add New Symbol for Rs. in that function and also in words some changes like : 50.56 (Rupees Fifty and Paise Fifty Six only) is may kindly be corrected as (Rupees Fifty and Fifty Six Paise only).

    I also requested you to please give me add-in for date to words function like : 29-12-2013 (Twenty Nine December Two Thousand Thirteen).
    my Email ID- bapibiru@gmail.com

    Frankly I also pay the requisite amount if it not free.

    Thanking you.

    J.K. Satapathy

    ReplyDelete
  41. Thanks.
    Also we can Try this Excel Add-in - CLICK
    http://www.xl.nikash.in/2012/06/convert-number-to-english-indian-rupees_8693.html

    ReplyDelete
  42. Dear
    Thanks for your add ins this great, work great
    thanks
    thanks once again
    Raj

    ReplyDelete
  43. Thanks sir,
    very useful and important addins which are provided by you
    Thanks lot again
    How we get the new addins or excel creations of your?

    ReplyDelete
  44. I have added Suresh Add Ins on a file that is shared on a server. It is not a shared work book, just located on a server where more that one computer/user has access to it. However the formula does not seem to work when opened on the shared computers unless I again go in to each location of the equaction rswords() and type it in again. Why is this?

    ReplyDelete
  45. I am using suresh ad ins for the rswords(). equation. I have it in stalled on a workbook that is saved on a server. However when I access the same work book on another computer that is on the server, the equation dosent work. It shows the path as ='\\Server\logos letterhead\SureshAddin.xla'!RSWORDS(E24). I have to each time then go to that users computer and re enter the equation as rswords() in each cell that it is required to be used in. How to I avoid this problem?

    ReplyDelete
  46. Thanks a lot.Its been lot of help.
    Hari Mohan

    ReplyDelete
  47. Thanks,

    It's vary useful one.
    Sir, I am using OPENOFFICE, is there such add in for open office too ?
    If it is please let me know how can I download and install it?
    Once again thanks.

    Dinesh Parmar

    ReplyDelete
  48. link not work plz do the needful...
    proveid the link

    ReplyDelete
    Replies
    1. download Link now updated http://taxclubindia.com/simple/371505_1142517_sureshaddin.xla

      Delete