Friday, October 30, 2009

Excel Number Format : Indian Style Comma Separation

Yesterday I got a comment asking for custom number format for Indian style comma separation. While I responded to question by providing the custom number format I knew that the answer does not handle the complete range of numbers and can not be applied in all the cases. This forced me to look around for a solution that can be applied for all kind of numbers.

Just for the information of people who are not aware of Indian Style of comma separation, I have produced table below which explains Indian style formatting for various numbers. The basic rule is that first comma separation happens at 3 digits i.e. 1000 then it happens after every 2 digits. Check out table below for better understanding.


Number
Formatted As
10
10.00
100
100.00
1000
1,000.00
10000
10,000.00
100000
1,00,000.00
1000000
10,00,000.00
10000000
1,00,00,000.00
100000000
10,00,00,000.00
1000000000
1,00,00,00,000.00
10000000000
10,00,00,00,000.00
100000000000
1,00,00,00,00,000.00

Soon I realised that there is no single custom format available for handling all kind of number length and we need separate format for it depending upon the number of digits. Doing this manually and accuratly every time is very difficult. This leaves only one option to automate this through a macro. I got a Macro code for doing this and modified and tested that for long numbers.

IndiaStyleCommaSeparation

I am sharing the macro code with you. You will need to select the numbers and run macro to format them as per India style comma sepration.


Sub IndianNumberFormat()
For Each c In Selection
Select Case Abs(c.Value)
Case Is < 100000
c.Cells.NumberFormat = "##,##0.00"
Case Is < 10000000
c.Cells.NumberFormat = "#\,##\,##0.00"
Case Is < 1000000000
c.Cells.NumberFormat = "#\,##\,##\,##0.00"
Case Is < 1000000000
c.Cells.NumberFormat = "#\,##\,##\,##0.00"
Case Is < 100000000000#
c.Cells.NumberFormat = "#\,##\,##\,##\,##0.00"
Case Else
c.Cells.NumberFormat = "#\,##\,##\,##\,##\,##0.00"
End Select
Next c
End Sub


For easy application of this code you need to save it as a excel addin or add it to your personal macro book. Adding a custom toolbar for this code will make it easy to apply. You can chage the number of decimal places by changing the number of zeros at the end of custom number format given above.
Posted by Yogesh Gupta at 5:29 PM
Labels: , , , ,

43 comments:

  1. Jon PeltierOctober 30, 2009 at 9:40 PM

    You can cut your routine in half. Change the For-Each to

    For Each c In Selection.Cells

    and remove everything from If to Else, plus End If.

    ReplyDelete
  2. Yogesh GuptaOctober 30, 2009 at 10:08 PM

    Hello Jon - Thanks for your inputs, I am updating the post above.

    ReplyDelete
  3. UnknownNovember 1, 2009 at 4:48 PM

    For Positive Numbers

    Sub Ind_Num_Fmt()
    For Each Cellval In Selection
    Cellval.NumberFormat = "[>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00"
    Next
    End Sub

    Sam

    ReplyDelete
  4. Yogesh GuptaNovember 1, 2009 at 7:49 PM

    Hi Sam

    Thanks for your input. However this can handle only positive numbers upto 100 Millions.

    That is why I have said that no single format can handle all kind of number lenght. This is the reason for using case statement to choose different format based on value.

    Thanks

    ReplyDelete
  5. UnknownNovember 2, 2009 at 9:00 PM

    You can get rid of that entire Select..Case block and replace it with a single (albeit long) statement...

    Sub IndianNumberFormat()
    For Each C In Selection
    C.NumberFormat = Trim(Replace(Format(String( _
    Len(Int(C.Value)) - 1, "#"), _
    " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), _
    " \,", "")) & ".00"
    Next C
    End Sub

    The above code should be able to handle up to a 15-digit positive or negative number.

    Rick Rothstein (MVP - Excel)

    ReplyDelete
    Replies
    1. Pravin GunjalJuly 1, 2013 at 10:32 AM

      By giving this the '0' zero is displaying as .00 instead of 0.00 or 0

      Pl look in to this.

      Delete
  6. UnknownNovember 3, 2009 at 2:26 AM

    By the way, instead of using a manually implemented macro, we can make this functionality automatic by using worksheet event code. Just right click the tab at the bottom of the worksheet that is to have this functionality, select View Code from the popup menu that appears and then copy/paste the following into the code window that appears...

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range
    Set R = Range("A:A,C3:E10")
    If Not Intersect(Target, R) Is Nothing Then
    With Target
    .NumberFormat = Trim(Replace(Format(String( _
    Len(Int(.Value)) - 1, "#"), _
    " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), _
    " \,", "")) & ".00"
    End With
    End If
    End Sub

    Just change the Range address in the Set statement to cover the range of cells you want to have this functionality. My sample above is set to apply the Indian Number Format to all of Column A plus just the cells in range C3:E10. After entering any number within this range of cells, it will be reformatted into the Indian style for a number.

    ReplyDelete
    Replies
    1. UnknownSeptember 18, 2012 at 7:24 PM

      FANTASTIC AND SUPERB .. I'VE SEARCHED IT FOR A LONG TIME.. A MARVELOUS CODE TO READ

      Delete
  7. Yogesh GuptaNovember 3, 2009 at 10:44 AM

    Hello Rick

    Thanks for your inputs. I am really amazed with your single line code to handle Indian Style comma separation for upto 15 digits number.

    These are great inputs and have really added value to my post.

    Thanks

    ReplyDelete
  8. UnknownNovember 3, 2009 at 9:06 PM

    You are quite welcome... I am glad that you liked my offerings. As for that single line of code solution... well, one-liners are kind of a "thing" with me. Before becoming an Excel MVP, I was a MVP for Visual Basic where I built up a reputation for writing one-liners. Google used to be able to show well over 1000 of them via a simple search (although I think I have written posted several thousand one-liners across the 9/10 years I have been volunteering answering newsgroup questions), but they changed something in their search engine a couple of years ago and now it is hard to find links to them. Anyway, this one-liner contains the same "challenge" for you and your readers that all my past posted ones did... try to figure out how and/or why it works.[grin]

    Rick Rothstein (MVP - Excel)

    ReplyDelete
  9. Yogesh GuptaNovember 3, 2009 at 10:46 PM

    Hello Rick

    I like the "challange" and let me explain the alog of your one liner for that let me break your code into various parts as it is doing mutiple line processing in single line due to its length.

    1. Most inner part of your one liner is building up string of # based on cell value. If value is 10 it is #. Incase it is 1000 string is ##. It is actually lengh of the int - 1.

    2. Formating this sting of # into " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0" . This builds indian style comma separation number format. However there are some " \," if the length of the int is less than 14.
    3. Replacing the " \," with ""
    4. Trim the string built after replace as it leaves space in the begining.
    5. Finaly adding ".00" for decimal points

    Now the entire string is ready as per Indian style comma separation format.

    I hope I have understood your code. Let me know if I have missed something.


    Regards

    ReplyDelete
  10. UnknownNovember 8, 2009 at 5:30 AM

    This comment has been removed by the author.

    ReplyDelete
  11. UnknownNovember 8, 2009 at 5:33 AM

    I'm totally amazed. How could I miss till now that indian punctuation is so different?
    Even the one liner to replace it is awesome!

    Thank you for sharing

    http://bit.ly/iUhlP

    ReplyDelete
  12. Yogesh GuptaNovember 8, 2009 at 10:34 AM

    Hi Augusto

    Thanks for your appreciative comments. This one liner is contributed by Rick Rothstein (MVP - Excel)

    Rick is very active on various forums and you can notice his contribution at various places on the net. When ever you serach any VBA help, you may get a code contributed by him.

    By this note I want to say Thanks to Rick, he has really added value to this post.

    Thanks
    Yogesh Gupta

    ReplyDelete
  13. UnknownNovember 14, 2009 at 2:37 PM

    Hi Yogesh,

    Although I said it in one of my earlier messages in this thread, I just wanted to say again that you are quite welcome and that I am glad you found my contribution of value.

    ReplyDelete
  14. UnknownMarch 24, 2010 at 10:45 AM

    Hi,

    for negative values one liner printed like this -,11,40,773 which needs to be corrected. Also it should retain original decimal values format, color and parenthesis for negative values. Can someone make the changes please.

    Thanks

    ReplyDelete
  15. Yogesh GuptaMarch 24, 2010 at 11:16 PM

    Hi Vijay

    As pointed out by you one liner had some issue with the negative numbers. I have corrected this, following code should be able to handle it correctly.

    Sub IndianNumberFormat()
    For Each C In Selection
    C.NumberFormat = Trim(Replace(Format(String( _
    Len(Int(Abs(C.Value))) - 1, "#"), _
    " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), _
    " \,", "")) & ".00"
    Next C
    End Sub

    However I am not sure about rest of the items pointed out by you.

    Thanks
    Yogesh Gupta

    ReplyDelete
  16. UnknownJuly 30, 2010 at 11:17 AM

    Yogesh,

    Let's revisit this old thread on formatting numbers in the Indian style. This requestion recently came up in another blog...
    -
    http://chandoo.org/wp/2010/07/26/indian-currency-format-excel/#comments
    -
    and I posted basically the same event code I posted here. One of the respondents make an side comment about it working for typed in text as well as formulas. Well, it really doesn't work for formulas, but the comment prodded me to modify the code so it could react to either... a number typed directly into the cells being monitored by the event code or, a formula in that range where the formulas displayed value changes because one of the cells referred to in the formula got changed. Here is that modified event code which I posted over in the other blog...
    -
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim R As Range, Cell As Range
    Set R = Range("C:C")
    If Not Intersect(Target, R) Is Nothing Then
    With Target
    If WorksheetFunction.IsNumber(.Value) Then
    .NumberFormat = Trim(Replace(Format(String(Len(Int(Abs(.Value))) - 1, "#"), _
    " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"
    Else
    .NumberFormat = "General"
    End If
    End With
    ElseIf Not Intersect(Target.Dependents, R) Is Nothing Then
    For Each Cell In Target.Dependents
    If Not Intersect(Cell, R) Is Nothing Then
    With Cell
    If .Value <> "" Then
    If WorksheetFunction.IsNumber(.Value) Then
    .NumberFormat = Trim(Replace(Format(String(Len(Int(Abs(.Value))) - 1, "#"), _
    " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"
    Else
    .NumberFormat = "General"
    End If
    End If
    End With
    End If
    Next
    End If
    End Sub
    -
    There is one line of code to maybe watch out for. I don't remember is this blog's comment processor removes "not equal to" symbols (a less than sign followed by a greater than sign. If you do not see a not equal sign in this line...
    -
    If .Value <> "" Then
    -
    then here is the line as it should be with a .NE. substituted for the less than/greater than symbols...
    -
    If .Value .NE. "" Then

    ReplyDelete
  17. shaunFebruary 19, 2011 at 4:20 PM

    very very thank....

    ReplyDelete
  18. Sidharth sarohaJuly 4, 2011 at 4:00 PM

    you says:-For easy application of this code you need to save it as a excel addin or add it to your personal macro book. Adding a custom toolbar for this code will make it easy to apply. You can chage the number of decimal places by changing the number of zeros at the end of custom number format given above. how i save it as a excel addin and add it to my personal macrobook. kindly reply my id is sidharthsaroha@gmail.com i will be thankful to you for experience sharing

    ReplyDelete
  19. Sidharth sarohaJuly 4, 2011 at 4:02 PM

    is there any contact no. and suitable time for which any quries.

    ReplyDelete
  20. Ishan KarveNovember 9, 2011 at 1:27 PM

    Thanks for the useful macro.

    ReplyDelete
  21. vijuNovember 29, 2011 at 6:27 PM

    There is no code available to convert numbers in Devanagari text e.g. 1 = एक, १०=दस.Code is likely to be more complicated for numbers from Twenty one to 99.Moreover vb does not accept Devanagari text written in Unicode.I shows as ?????.

    ReplyDelete
  22. Rajendraprasad KamuniFebruary 2, 2012 at 1:42 AM

    Hi Yogesh,

    I have 2000 rows of data. They have with some products names and values. I would like to add comma before any value(number) in the data without typing. Kindly please suggest me how could I do this ?

    ReplyDelete
  23. syeddiwanMarch 8, 2012 at 12:32 PM

    Hi Yogesh,

    Thanks,It's very use full. But It will not working for negative number.Can you pls guide me..

    ReplyDelete
    Replies
    1. Yogesh GuptaMarch 9, 2012 at 9:39 PM

      Following code will take care of negative numbers


      Sub IndianNumberFormat()
      For Each C In Selection
      C.NumberFormat = Trim(Replace(Format(String( _
      Len(Int(Abs(C.Value))) - 1, "#"), _
      " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), _
      " \,", "")) & ".00"
      Next C
      End Sub

      Delete
    2. UnknownOctober 17, 2014 at 2:03 PM

      can the negative numbers be shown in bracket (12,23,245) instead of -12,23,345?

      Delete
  24. GaUrAvMarch 9, 2012 at 10:12 PM

    hello Yogesh SIR. i am commission agent[artia] of punjab. i am looking for software related to mandi. we ve to purchase the goods from all partyies of mandi and send it to that miller. i am looking for sogtware which maitain inventory party wise and also stock lifted both party wise and that station which that truk is sended. and in the end we ve to make interest of all partyies. but i am not geeting the right i want . please help me.

    ReplyDelete
  25. SnehaMarch 29, 2012 at 10:11 AM

    hi,
    in excel there is an option to display -ve numbers in brackets. Ex: -5,200.00 will be displayed as (5,200.00). is it possible to insert this feature into the code u have given in the beginning.

    otherwise this code was very helpful.
    Thank you.

    ReplyDelete
  26. AlagappanMay 29, 2012 at 5:00 PM

    This comment has been removed by the author.

    ReplyDelete
  27. sureshNovember 27, 2012 at 5:44 AM

    one of my official task is to calculate the number of working days between two dates provided, sunday not included, saturday considered as half a day and a set of 15 holidays spread over in a year like new year, pongal, republic day, independence day, gandhi jayanthi, diwali etc. I would be much obliged if you could help me in designing a custom function.
    suresh

    ReplyDelete
  28. UnknownDecember 31, 2012 at 11:06 AM

    Thanx this code was helpful for me to converting numbers in Indian Format.

    ReplyDelete
  29. UnknownJanuary 2, 2013 at 4:04 PM

    Thank u so much... it absolutely helpful for me

    ReplyDelete
  30. Pravin GunjalJune 28, 2013 at 10:43 AM

    Absolutely amazing..

    ReplyDelete
  31. UnknownJuly 30, 2013 at 4:00 PM

    The easiest way is keep excel close and
    1. Goto the Control panel and select the “Region and Language” button
    2. Select from drop down English - India
    3. Apply & close.

    and then See the magic !!!!!!!!!

    ReplyDelete
  32. UnknownJune 24, 2014 at 4:33 PM

    Awesome man.... No need to take out your heart on coding...... simply follow 3 steps and finish....

    Thnx a ton Mr. Imran.

    ReplyDelete
  33. UnknownJuly 26, 2014 at 11:08 PM

    [>=10000000]##\,##\,##\,##0.00;[>=100000] ##\,##\,##0.00;##,##0.00

    ReplyDelete
  34. Sivaram NayuduSeptember 14, 2014 at 5:42 AM

    Can someone post the final version of either the macro or the custom number/accounting format? It would be nice if the formatting also included the new rupee currency symbol.

    ReplyDelete
    Replies
    1. UnknownJanuary 9, 2015 at 11:21 PM

      Did you ever get a reply to this post? I too am in need of the completed macro with INR symbol.

      Delete
    2. Yogesh GuptaJanuary 10, 2015 at 1:07 PM

      Use following code, this would work as per your need.

      Sub IndianNumberFormat()
      For Each c In Selection
      c.NumberFormat = """INR """ & Trim(Replace(format(String( _
      Len(Int(c.Value)) - 1, "#"), _
      " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), _
      " \,", "")) & ".00"

      Next c
      End Sub

      Delete
  35. allenDecember 4, 2014 at 4:43 PM

    Goto control panel

    Then goto Region and Language

    Then Customise Format

    Then Currency

    Digit Grouping

    Then in Excel Formal Cell, select Numbers & use 1000 Separator

    ReplyDelete
  36. UnknownOctober 6, 2015 at 8:07 PM

    appreciate it

    ReplyDelete

Subscribe to: Post Comments (Atom)