Excel Number Format : Nepali Style Comma Separation

Excel Number Format : Nepali Style Comma Separation

Yesterday I got a comment asking for custom number format for Nepali 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 Nepali 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 Nepali style comma sepration.

Sub NumberFormat() 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.