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.

**Sarav Sood** · 11-02-2012, 03:32 PM

**Rick Rothstein** · 11-05-2012, 08:10 PM

Originally Posted by Sarav Sood

Thx for this VB code. It is useful, but Can there be any option to permanently save this code for every excel.
I have to put this code each & every time in many excels. I want to run this code in every excel I open or run.

**shajithm** · 07-30-2013, 09:54 AM

**Rick Rothstein** · 07-31-2013, 02:49 AM

Originally Posted by shajithm

Hi Ricky,
Thanx for the code, highly appreciate the splendid job. Its very useful for me
can u also help me to add a prefix for Indian rupee symbol or "INR" to the value on which i have to run the code.
thanx once again.

**Sheeloo** · 09-01-2013, 05:15 PM

Originally Posted by Rick Rothstein

I am glad you found the code useful. I am not actually familiar with the way Indian money is displayed... does the rupee symbol and the INR go in front or in back of the number? Is there a space separating them? Would you also copy/paste the rupee symbol into your response somewhere so I can see what it looks like?

Hello Rick,

I stumbled upon this link via Microsoft Answers... just in case you still need the information.

The INR symbol is displayed BEFORE the amount. Here is the symbol rupee-symbol-small.jpg

Strangely enough (to the best of my knowledge) there was no symbol till last year. Rs. was used to denote Rupees instead of a symbol.