Number Separator

[Guest]

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

 

[Norman Harker]

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

 

[Myrna Larson]

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.