?
Solved

Indian Number format in MS-Excel

Posted on 2003-03-04
7
Medium Priority
?
4,017 Views
Last Modified: 2008-01-16
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
Comment
Question by:rajuonline
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 5

Expert Comment

by:bullethead
ID: 8063772
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
 
LVL 2

Author Comment

by:rajuonline
ID: 8101659
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
 
LVL 5

Accepted Solution

by:
bullethead earned 80 total points
ID: 8101769
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 2

Author Comment

by:rajuonline
ID: 8101829
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
 
LVL 5

Expert Comment

by:bullethead
ID: 8101924
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
 
LVL 2

Author Comment

by:rajuonline
ID: 8117835
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
 
LVL 5

Expert Comment

by:bullethead
ID: 8118022
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article was originally published on Monitis Blog, you can check it here . If you have responsibility for software in production, I bet you’d like to know more about it. I don’t mean that you’d like an extra peek into the bowels of the sourc…
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
The viewer will learn how to create multiple layers to apply various filters and how to delete areas from each layer’s filter.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question