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.



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.

Excel Number Format : Telephone numbers with leading + sign

This is in continuation of my earlier post Custom Formating - Excel Number Format

Excel does not display the leading + sign entered by users, similarly the leading zeros are also not recoganised by excel. However custom formating trick can help you do this. Refer to the following table, where in Phone number 9971112814 has been formated differently to display different formats for the same cell contents.

91 is country code for India, you can change it to whatever code you want to display the number accordingly.

Format	Displayed as
+91 0000000000	+91 9971112814
+91(0)0000000000	+91(0)9971112814
+91(0)000-000-0000	+91(0)997-111-2814
0091 0000000000	0091 9971112814

Custom Formating - Excel Number Format

Excel stores numbers as normal numbers only, however you can view them as you want based on how do you format the cells.



If you see there are pre defined number format options available when you choose cell format option, however you can use custom format option if available formats does not server your requirement.



There are four sections of format codes. The sections are separated by semicolons ";" First section defines the formats for positive numbers; Second section defines negative numbers; third section defines how to show zero values; and forth one define how to diplay text. If you specify only two sections, the first is used for positive numbers and zeros, and the second is used for negative numbers. If you specify only one section, all numbers use that format. If you want to skip a section, include the ending semicolon for that section.

# displays only significant digits and does not display insignificant zeros.

0 (zero) displays insignificant zeros if a number has fewer digits than there are zeros in the format.

? adds spaces for insignificant zeros on either side of the decimal point so that decimal points align.

? can also be used for fractions that have varying numbers of digits.

Special Symbol Shortcuts Using the ALT Key

There are some special symbols which we need to use many times but they are not available on the keyboard. We can type them using ALT key and numeric code for the particular symbol

Pls see the chart for numeric codes for various symbols. If you need to type £ you need to press ALT key and keep it pressed while typing 0163. Release ALT key once you have typed 0163. Immediately on release of ALT key you will see £ sign appearing on your screen. You can follow the same process for getting other symbols by typing the relevant numeric code.

Custom Formating - Excel Date Format

Microsoft Excel stores dates as sequential numbers known as serial values. Excel stores times as decimal fractions because time is considered a portion of a day.

You can view them as you want based on how do you format the cells. There are predefined date formats available when you choose format cells option. However you can use custom format option to display dates if available formats does not serve your requirement.



You can choose following codes or combination of it to view date in excel as you want. I have used highlighted codes dd mm yyyy with "/" to show today's date as 16/02/2009 in above screen. You can use any of these code singly or jointly with any character in between.

Formating cells in Excel

Data entered in excel shows differently on your worksheet depending upon the cell format used by you.


January 19, 2009 which is stored by system as 39832 can shown as 19-01-2009 or 19-Jan-2009 or
19-Jan-09. There are many combination that can be achieved by changing the cell format for the cell containing that value.


Similarly you can add currency sign such as Rs / $ / £ / ¥ , it can be any sign of your choice.

Not only this you can actually add color codes the numbers depending upon the value.


You can choose all these options from Format Cell Menu. You need to choose Number tab than category as per requirement of the format.

Most tricky / dynamic category is Custom format at the end of the list. How to use this in more productive way will be covered in later posts on the specific subjects like custom formating dates, custom formating numbers etc in excel.