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
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
njha...@optusnet.com.au
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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.