Thursday, February 11, 2010

Convert currency to word in excel

There are plenty of online resources to convert Numeric currencies to Text- from $200 to Dollar Two Hundred.
But this is applicable only ###,###,### (comma at every third number)format.  This is not suitable for India numbering system, because we follow ##,##,###(....22,22,222) format.

So I have create a excel function to over come this. You can use this function to to convert number up to 10,00,00,000(ten crores).

How to do it

Open Excel

Press Alt + F11

From Menu Bar Insert Module

Copy paste the This Module there.

Exit from VBA window by pressing Alt + F11 again

In excel sheet, Insert Function,


Under user defined find ConvertCurrencyToEnglish 


select it and provide the number as a parameter

thats all you are done
----------------------------------------------------------------------
Code (alternatively you can download the module from here)
----------------------------------------------------------------------


' ****  Author                 : Krishna S
' ****  Tittle                    : Converting Hindu Arabic Currency(Indian System) to Words
' ****  Description          : This utility converts currencies in Indian numbering system to words.
' ****  Limitations           : Converts only upto 10,00,00,000( Ten Crores)

Function ConvertCurrencyToEnglish(ByVal MyNumber)
Dim Temp
         Dim Rupees, Paise
         Dim DecimalPlace, Count
         ReDim Place(9) As String
         Place(2) = " Thousand "
         Place(3) = " Lac "
         Place(4) = " Core "
      '   Place(5) = " Hundred Core "
         ' Convert MyNumber to a string, trimming extra spaces.
         MyNumber = Trim(Str(MyNumber))
         ' Find decimal place.
         DecimalPlace = InStr(MyNumber, ".")
         ' If we find decimal place...
         If DecimalPlace > 0 Then
            ' Convert Paise
            Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
            Paise = ConvertTens(Temp)
            ' Strip off Paise from remainder to convert.
            MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
         End If
         Count = 1
        
         Do While MyNumber <> ""
                 If Count = 1 Then
              
                   Temp = ConvertHundreds(Right(MyNumber, 3))
                    
                    If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
                    If Len(MyNumber) > 3 Then
                       ' Remove last 3 converted digits from MyNumber.
                       MyNumber = Left(MyNumber, Len(MyNumber) - 3)
                    Else
                       MyNumber = ""
                    End If
                    Count = Count + 1
                 Else
                 ' Convert last 3 digits of MyNumber to English Rupees.
                 Temp = ConvertTens(Right(MyNumber, 2))
                
                    If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
                    If Len(MyNumber) > 3 Then
                       ' Remove last 3 converted digits from MyNumber.
                       MyNumber = Left(MyNumber, Len(MyNumber) - 2)
                    Else
                       MyNumber = ""
                    End If
                    Count = Count + 1
                    End If
         Loop
         ' Clean up Rupees.
         Select Case Rupees
            Case ""
               Rupees = ""
            Case "One"
               Rupees = "One Rupee"
            Case Else
               Rupees = Rupees & " Rupees"
         End Select
         ' Clean up Paise.
         Select Case Paise
            Case ""
               Paise = ""
            Case "One"
               Paise = " And One Cent"
            Case Else
               Paise = " And " & Paise & " Paise"
         End Select
         ConvertCurrencyToEnglish = Rupees & Paise
End Function
Private Function ConvertHundreds(ByVal MyNumber)
Dim Result As String
         ' Exit if there is nothing to convert.
         If Val(MyNumber) = 0 Then Exit Function
         ' Append leading zeros to number.
         MyNumber = Right("000" & MyNumber, 3)
         ' Do we have a hundreds place digit to convert?
         If Left(MyNumber, 1) <> "0" Then
            Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
         End If
         ' Do we have a tens place digit to convert?
         If Mid(MyNumber, 2, 1) <> "0" Then
            Result = Result & ConvertTens(Mid(MyNumber, 2))
         Else
            ' If not, then convert the ones place digit.
            Result = Result & ConvertDigit(Mid(MyNumber, 3))
         End If
         ConvertHundreds = Trim(Result)
End Function
Private Function ConvertTens(ByVal MyTens)
Dim Result As String
         ' Is value between 10 and 19?
         If Val(Left(MyTens, 1)) = 1 Then
            Select Case Val(MyTens)
               Case 10: Result = "Ten"
               Case 11: Result = "Eleven"
               Case 12: Result = "Twelve"
               Case 13: Result = "Thirteen"
               Case 14: Result = "Fourteen"
               Case 15: Result = "Fifteen"
               Case 16: Result = "Sixteen"
               Case 17: Result = "Seventeen"
               Case 18: Result = "Eighteen"
               Case 19: Result = "Nineteen"
               Case Else
            End Select
         Else
            ' .. otherwise it's between 20 and 99.
            Select Case Val(Left(MyTens, 1))
               Case 2: Result = "Twenty "
               Case 3: Result = "Thirty "
               Case 4: Result = "Forty "
               Case 5: Result = "Fifty "
               Case 6: Result = "Sixty "
               Case 7: Result = "Seventy "
               Case 8: Result = "Eighty "
               Case 9: Result = "Ninety "
               Case Else
            End Select
            ' Convert ones place digit.
            Result = Result & ConvertDigit(Right(MyTens, 1))
         End If
         ConvertTens = Result
End Function
Private Function ConvertDigit(ByVal MyDigit)
Select Case Val(MyDigit)
            Case 1: ConvertDigit = "One"
            Case 2: ConvertDigit = "Two"
            Case 3: ConvertDigit = "Three"
            Case 4: ConvertDigit = "Four"
            Case 5: ConvertDigit = "Five"
            Case 6: ConvertDigit = "Six"
            Case 7: ConvertDigit = "Seven"
            Case 8: ConvertDigit = "Eight"
            Case 9: ConvertDigit = "Nine"
            Case Else: ConvertDigit = ""
         End Select
End Function
-----------------------------------------------------------

29 comments:

  1. Good dispatch and this enter helped me alot in my college assignement. Say thank you you for your information.

    ReplyDelete
  2. your code doenot work between 1lac and ten lac

    ReplyDelete
  3. your code doenot work between 1lac and ten lac great work any ways

    ReplyDelete
  4. your's was not working between 1000 to 2000

    ReplyDelete
  5. your's cood was not working between 1000 to 2000 & 100000 to 100000

    ReplyDelete
  6. Hi All,
    thanks for your comments

    this code works upto 10,00,00,000 digits.

    if you do some tweaks then it can be used beyond that.

    please post your comments for any information

    thanks
    krishna

    ReplyDelete
  7. hey guys did you see the version 2.0?

    ReplyDelete
  8. Please post version 2.0

    ReplyDelete
  9. it is not working for the numebr 9827
    some what to impliment the code
    pls do the needful

    ReplyDelete
  10. Hi Krishna,
    Just see my blog, where i am solve this Visual Basic error, and it's working fine..

    Find it in my blog..
    http://sumansau007.blogspot.com/2011/06/convert-number-to-words-in-microsoft.html

    Please leave a comment in my blog for any query.

    Cheers
    Suman

    ReplyDelete
  11. how we will save this vba module in excell 2007 so it work by default in each excell 2007 workbook

    ReplyDelete
    Replies
    1. you need to make it as a template

      Delete
    2. how do we make the template?

      Delete
    3. I will post the template soon..

      Delete
  12. Hi all, I have created a Excel add-in to make this macro available to the entire Excel. see this post
    http://krishnasr.blogspot.in/2012/11/this-post-will-show-you-how-to-makean.html

    ReplyDelete
  13. Υou can certainlу see yоur skills in the wοrκ
    yοu writе. The sector hopes for еven more passiοnatе
    ωriterѕ like yоu whο аre not аfrаid to mention hоw thеy
    believe. At all times follow your heаrt.
    My web site > weneedsnews.altervista.org

    ReplyDelete
  14. Ӏt іѕ crаfted up from alternatiνe
    grades οf small-scаle mixturе that hаvе been preѵiously coatеd іn ωаrm bitumen that acts аs a bіndеr whеn combіneԁ totally with inсredibly hοt
    аsphаlt. - Centгe tunnel сhimney duсt to
    maximise effective scoгchіng airflоw.
    "In modern society, I necessarily mean half the time various human beings have do not ever been by yourself, actually by itself, not having some sort of distraction.
    Feel free to surf my web site ; baker catalogue baking stone old stone oven

    ReplyDelete
  15. Definitely bеliеve that which уоu ѕtated.
    Your favorite reason seemed to bе on the web the
    sіmplest thing tο be aware of. I saу to you,
    I certainly get irkeԁ while people considеr wοrrіeѕ that thеy just dо not knoω about.

    You manageԁ to hit the nail upon the top anԁ definеd οut the whole thing wіthout having ѕiԁе-effectѕ , ρeople
    can takе a signаl. Wіll probably be back tο get mοre.
    Thanks

    my web рage ... http://meevle.com/lenne/wiki/index.php?title=Overleg_gebruiker:JenniferG

    ReplyDelete
  16. Definitely believe that whіch you stated.
    Your fаvorite reaѕon ѕееmed tο be on the ωeb the simрlеst thing
    to bе aωare οf. І sаy tο уou, I сertainly get irked while рeople сοnѕidеr ωorгiеs that theу јust do not knοw аbout.

    Yοu managed tο hіt the nail upon the tоp and ԁefined out thе ωhole thіng without hаvіng
    side-effects , реople can takе a signаl.
    Will probablу be bаcκ to get moгe.
    Thanks

    Herе is my web раgе; http://meevle.com/lenne/wiki/index.php?title=Overleg_gebruiker:JenniferG
    Also see my web site - Http://Bottomsupantiques.Com/

    ReplyDelete
  17. Allas, At last I have find some information which was needy. After Converting the the documents. Only the necessary changes are to made and document are ready for Convert to HTML. Very Helpful Blog. Keep writing.

    ReplyDelete
  18. I drop a leave a resρonse eаch time I appгeciate
    a ρost on a wеbsіte oг if I have something
    to аdd to the сonversаtіοn.
    It's triggered by the fire communicated in the article I browsed. And after this post "Convert currency to word in excel". I was actually excited enough to drop a comment ;-) I do have a couple of questions for you if it'ѕ аllгight.

    Ϲould it be οnly mе or ԁо a
    few of the commеnts lοok like left by braіn deаd fοlks?
    :-P And, if you агe writing on adԁitіonаl ѕocial sіteѕ, I'd like to keep up with you. Would you make a list all of all your public pages like your Facebook page, twitter feed, or linkedin profile?

    My web-site Chemietoilette

    ReplyDelete
  19. Mу spirit moved abѕent аbout 3 manу years in thе
    ρast my oνerаll bοdу experienced
    the couгage to last but not lеаѕt finish thіѕ sagа, and I did сhоose
    Ι was much bеtter obtained іn the
    city I last of all ροsіtioned tо anԁ as
    foг the suρerior fortune Ӏ wаѕ sеarching
    for, I detеrmined what Jοseph Camρbell wrote waѕ true:
    &#8220Your full actual phуsical strategy undeгstanԁs that this is thе way
    to be alіve in this world аnԁ the ωay to give the еspecially
    ultіmate that уou have to fеatuге.

    - Centre tunnel сhimney duct tο maximіse successful wагm airflοw.
    The blοοd of thosе who are living a lifetime οf eat, drinκ аnd be merrу is
    simple аnd their reѕρігatorу is vеrу raρiԁ.


    My wеb-sіte Round pizza Stone with handles

    ReplyDelete
  20. Тгemendous things here. Ι am vеry ѕatіsfiеd
    to рeer yοur article. Thanκ you a lot and I'm looking ahead to touch you. Will you kindly drop me a e-mail?

    Have a look at my webpage: http://Qugenswohio.Blogspot.ru/2005/07/this-map-graphically-illustrates.html

    ReplyDelete
  21. I thinκ thiѕ is οne of thе
    most vіtal infο for me. And i'm glad reading your article. But should remark on some general things, The web site style is ideal, the articles is really great : D. Good job, cheers

    Here is my blog post daniele-negroni.blogspot.com

    ReplyDelete
  22. Remarkable! Its genuinely remarkable article, I have got much
    clear idea concerning from this post.

    My blog :: Kindergeburtstag Mannheim

    ReplyDelete
  23. can u make it for all currencies without consistent to rupee. bcz i want to use same sheet for currencies

    ReplyDelete
  24. Hi, Thanks.
    You can also Try Gword Excel Add-In
    CLICK- http://www.xl.nikash.in/2013/08/excel-to-tally-data-xml-format_2797.html

    ReplyDelete
  25. I would like to thank you for the efforts you have made in writing this interesting and knowledgeable article.
    Hp Laptops

    ReplyDelete
  26. Try...
    Free Excel Add-in Convert Excel cell Numbers to Words with Prefix and Suffix features.
    Download Link - http://www.xltool.in

    ReplyDelete