Excel Number Format: Indian Style Comma Separation

**Rick Rothstein** · 04-12-2012, 11:08 PM

The Indian style of comma separating large numbers seems kind of odd to me given my US-centric view of the world, but there is no denying that a large segment of the planet uses it. If you are unfamiliar with the Indian comma-style number format, then here is an example along with the "normal" method of comma separating numbers...

Western Style: 123,456,789,012

Indian Style: 1,23,45,67,89,012

In the Indian style, only the first three right-most digits (excluding decimal values if any) are grouped together, all other digits (if any) appear in groups of at most two digits, with commas used to separate the groups. Unfortunately, Excel provides no built in way to handle this style of formatting, so we must rely on VB code to provide it. I guess I should point out that there is a Custom Cell Format which can be used on numbers less than 1000000000, namely, this one (to two decimal places)...

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

but if your numbers could equal or exceed 1000000000, then you are out of luck. The following code procedures will work correctly on numbers between -99999999999999 and 999999999999999 (that is, up to a maximum of 15 total digits for positive numbers and 14 total digits for negative numbers).

This procedure is a macro and, as written, it operates on the numbers in the currently selected range of cells; however, you can change the Selection reference in the For..Each statement to a specific range of cells if that is your need.

Code:

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

If you would rather have the formatting operation be automatic, similar to the way Cell Formatting works, then you need to use event code instead of a macro. If your numbers are always going to be constants (see Follow-up to "Excel Number Format: Indian Style Comma Separation" for a more general event procedure able to handle both constants and formulas), then you can use this simple event procedure to automatically format the specified range of cells...

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim R As Range, Intersection As Range, Cell As Range
  
  ' You set the range of cells that will be formatted with the Indian Comma Style here
  ' For this example, any cell in Column A or in the cells in E3:H10 will be formatted
  Set R = Range("A:A,E3:H10")
  
  Set Intersection = Intersect(Target, R)
  If Not Intersection Is Nothing Then
    For Each Cell In Intersection
      Cell.NumberFormat = Trim(Replace(Format(String(Len(Int(Cell.Value)) - 1, "#"), _
                          " @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", "")) & ".00"
    Next
  End If
End Sub

INSTALL MACROs
---------------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (IndianNumberFormat) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for.

INSTALL Event Code
--------------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself.