Numeric Digit Grouping in Excel 2002

  • Thread starter Thread starter Omprakash Agarwal
  • Start date Start date
O

Omprakash Agarwal

Please let me know me how to format the cells for
grouping in the Excel-2000
Example Rs.12,34,56,789.00 (Indian currency, figures to
be read in thousand, lakhs, and crores

Thanks in Advance
Omprakash Agarwal
 
Hi Omprakash!

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 a Worksheet_Change event handler that you can adapt for a
particular range:

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
 
Back
Top