• Status: Solved
• Priority: Medium
• Security: Public
• Views: 4023

# Indian Number format in MS-Excel

The MS Excel uses the thousand separator by inserting a comma after each 3 digits. i.e. the number format in Execl is 999,999,999,999.00. But Indian number format is not like this, it has a comma after first 3 digits (after thousand) and then comma after each 2 digits. So Indian number format is like 99,99,99,999.00. Now I want to know how to write a custom number format / macro to produce this.
0
rajuonline
• 4
• 3
1 Solution

Commented:
Go to Format Cells, and on the Number tab click on custom.

Define the custom format as:

##","##","##","##","###.00

The inverted commas stop Excel seeing the comma as a thousands separator, and force it to show as you desire.
0

Author Commented:
Thanks Bullethead, I tried this but not usable. If i foramt the number 1234567.00 this way, it will  appear as ,,12,34,567.00. This formula leaves leading commas if the length of the number is smaller than the length of the formula. So for each cell i need to insert the formula manually.
0

Commented:
Okay, I have put together a little piece of code that will save you from having to do this manually.  In order to use it you need to open the VB Editor (Alt+F11), click on Insert\Module and copy and paste the code into it.

If you select the cells you want to format and click on Tools\Macro\Run and select this macro you should find that it works (I hope).

Sub FormatNos()
On Error Resume Next

For Each c In Selection
mylen = Len(Left(c.Value, Application.WorksheetFunction.Find(".", CStr(c.Value))))
If Err Then
mylen = Len(c.Value)
Err.Clear
End If
strformat = "###.00"
If mylen > 3 Then
mytot = Round(mylen - 3 / 3, 0)
For i = 1 To mytot - 2 Step 2
strformat = "##" & Chr(34) & "," & Chr(34) & strformat
Next
End If
c.NumberFormat = strformat
Next

End Sub

If you have any problems please get back to me...
0

Author Commented:
Thanks bullethead, it works fine. I have happilly removed the default comma button from the toolbar and added a new button assigning this macro.
(the only problem with this is that it is not a dynamic format, so if the value of the cell changes, the format does not change, so little bit manual intervention is still needed.)
0

Commented:

I can't think of a good way to make it dynamic.  There are VBA events that can trigger a macro whenever a sheet is changed, however it would be difficult to define which cells were to be updated.
0

Author Commented:
I used the solution for some days and made this observations..
1. it still puts leading commas before negative numbers, eg. -12345.00 will appear as ,12,345.00, because the code calculates the length upto decimal point.
2. Indian number has another problem,the currency symbol is Rs. (Rupees),but if the value is 1 than it will be Re. (Rupee), I had an one line macroto do this, but your code overides this format.

so i have made this modifications to your code to solve this problem.

Sub FormatNos()
On Error Resume Next

For Each c In Selection
mylen = Len(Round(Abs(c.Value), 0))
If Err Then
mylen = Len(c.Value)
Err.Clear
End If
strformat = "###.00"
If mylen > 3 Then
mytot = Round(mylen - 3 / 3, 0)
For i = 1 To mytot - 2 Step 2
strformat = "##" & Chr(34) & "," & Chr(34) & strformat
Next
End If

c.NumberFormat = "[=1]" & Chr(34) & "Re. " & Chr(34) & "* " & strformat & " " & ";" & "[<0]" & "\$* " & "(" & strformat & ")" & ";" & "\$* " & strformat & " "

Next
End Sub

At present it is working fine.please let me know is it ok or not? i don't know VBA - just record macros, I just used your code as reference.
0

Commented:
Yes that looks fine.

I can't test it myself because of my regional settings I think - but if it works for you go for it!
0

## Featured Post

• 4
• 3
Tackle projects and never again get stuck behind a technical roadblock.