If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Number Separator
an U help me out,,,,
Number separator of the excel according to the Indian Standard For e.g. 1000 is displayed as 1,000 10000 is displayed as 10,000 100000 is displayed as 100,000 but I want in 1,00,000 1000000.......1,000,000 but i want 10,00,000 and it goes on Same witht he negative numbers where I want -100000 to be (10,000) -1000000 to be (1,00,000) etc.... We can go to the Format-custom- and then a logic should be there to customize it .....Anybody clear on this? Thanks & Regards, Vishal Khemka |
#2
|
|||
|
|||
Number Separator
Hi Vishal!
Unfortunately, even in the Indian version of Excel, these formats are not provided. Here are some custom currency formats originally posted by Bill Manville. Rupees with Paise [9999999]"Rs."##\,##\,##\,##\,##0.00;[99999]"Rs."##\,##\,##0.00;"Rs."##,##000 Rupees without Paise [9999999]"Rs."##\,##\,##\,##\,##0;[99999]"Rs."##\,##\,##0;"Rs."##,##0 Rupees without Rs. notation [9999999]##\,##\,##\,##0.00;[99999]"##\,##\,##0.00;"##,##0.00 And here's some code that allows negative numbers to be formatted as well: Private Sub Worksheet_Change(ByVal Target As Range) If Application.Intersect(Target, Range("A1:A20")) Is Nothing Then Exit Sub End If With Target Select Case Len(Abs(Int(.Value))) Case Is = 3 .NumberFormat = "###.00;(###.00)" Case Is = 5 .NumberFormat = "##,###.00;(##,###.00)" Case Is = 7 .NumberFormat = "#\,##\,###.00;(#\,##\,###.00)" Case Is = 9 .NumberFormat = "#\,##\,##\,###.00;(#\,##\,##\,###.00)" Case Is = 11 .NumberFormat = "#\,##\,##\,##\,###.00;(#\,##\,##\,##\,###.00) " End Select End With End Sub -- Regards Norman Harker MVP (Excel) Sydney, Australia |
#3
|
|||
|
|||
Number Separator
I searched Google for "Indian number format" (with the quotes) and found the
following link http://www.exceltip.com/st/q/634html which gives a custom format. The following is extracted from that web page. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ this solution ... shows the additional commas even if there are no digits beyond that [i.e. to the left]... Format: ###\,##\,##\,### Since, the comma/s will be visible even if the number is less than 1 crore for example, Leading zeros can be used. Format: 000\,##\,##\,### ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ As far as getting rid of the leading commas, conditional formatting is limited to a maximum of 3 conditions, so that won't handle the situation. Sub FormatIndian() Dim c As Long Dim Cell As Range Dim i As Long Dim x As Variant Const sFmt As String = "##\,##\,##\,##\,##\,##0.00" For Each Cell In Selection x = Cell.Value If IsNumeric(x) Then i = Len(Format$(Abs(Fix(x)))) If i 3 Then c = ((i - 2) \ 2) * 2 Else c = 0 End If Cell.NumberFormat = Right$(sFmt, i + c + 3) End If Next Cell End Sub If you called this from an event macro (Worksheet_Change and/or Worksheet_Calculate), it would run automatically. In that case it would need changes to specify the range rather than operating on the selected cells. On Fri, 25 Jun 2004 23:19:25 -0700, wrote: an U help me out,,,, Number separator of the excel according to the Indian Standard For e.g. 1000 is displayed as 1,000 10000 is displayed as 10,000 100000 is displayed as 100,000 but I want in 1,00,000 1000000.......1,000,000 but i want 10,00,000 and it goes on Same witht he negative numbers where I want -100000 to be (10,000) -1000000 to be (1,00,000) etc.... We can go to the Format-custom- and then a logic should be there to customize it .....Anybody clear on this? Thanks & Regards, Vishal Khemka |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Job Number Match function??????? | Russ | Worksheet Functions | 3 | April 19th, 2004 06:51 PM |
Excel- Inserting a varying number of rows | Ken Wright | Worksheet Functions | 1 | March 20th, 2004 10:20 PM |
make a function that add number 1 in another number | Manos | Worksheet Functions | 3 | February 3rd, 2004 01:37 PM |
Counting number of occurrances | martin | Worksheet Functions | 0 | January 8th, 2004 12:19 AM |
Rounding Functions | Teacher | Worksheet Functions | 2 | January 7th, 2004 02:27 PM |