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

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
Asked:
rajuonline
  • 4
  • 3
1 Solution
 
bulletheadCommented:
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
 
rajuonlineAuthor 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
 
bulletheadCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
rajuonlineAuthor 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
 
bulletheadCommented:
Glad it worked for you.

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
 
rajuonlineAuthor Commented:
Bullethead ....
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
 
bulletheadCommented:
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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