Numerical Grouping of 10,00,000.00 lacs not possible in Excel

[Guest]

It has been observed that when numbers are required to be grouped for India
it is not possible in Excel. Even though when you select the regional
settings for Metric this is not used as defalt by Excel. The tragedy is when
you go to regional settings India as a country does not exist on this list
and nither does its currency symble.

I would appreciate if some one has a solution for this please send the same
to me as to read quickly with Million grouping is difficult as day to day we
aqre used to lacs and Crores and not millions and billions.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...a-d523ea279588&dg=microsoft.public.excel.misc

 

[Guest]

This is possible in excel. One of friends used to do this. But I am not able
to contact him now.
And this is not a bug in MS Excel.

 

[Bob Phillips]

Use a custom format of


[>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00

will format say 1234567890 as 123,45,67,890.00

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

It has been observed that when numbers are required to be grouped for India
it is not possible in Excel. Even though when you select the regional
settings for Metric this is not used as defalt by Excel. The tragedy is when
you go to regional settings India as a country does not exist on this list
and nither does its currency symble.

I would appreciate if some one has a solution for this please send the same
to me as to read quickly with Million grouping is difficult as day to day we
aqre used to lacs and Crores and not millions and billions.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...a-d523ea279588&dg=microsoft.public.excel.misc

 

[Guest]

Dear Bob Phillips
The solution you have given works upto only 7 digits. HOw to make
it generic or upto 11 digits.

 

[Bob Phillips]

It works upto 9 digits, but can't do 11 as there is only two conditions
allowed in the format.

You could do it with event code

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "A1:H10"
Const INDIAN_FORMAT As String = _
"[>=10000000]##\,##\,##\,##0.00;[>=100000]##\,##\,##0.00;##,##0.00"
Dim i As Long
Dim tmp

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = Replace(.Value, ",", "")
If InStr(.Value, ".") > 0 Then
.Value = Left(.Value, Len(.Value) - 3)
End If
If Len(.Value) <= 9 Then
tmp = Format(.Value, INDIAN_FORMAT)
Else
tmp = Format(Right(.Value, 9), INDIAN_FORMAT)
.Value = Left(.Value, Len(.Value) - 9)
Do
tmp = Format(Right(.Value, 2), "##\,") & tmp
.Value = Left(.Value, Len(.Value) - 2)
Loop Until .Value = ""
End If
.NumberFormat = "@"
.Value = tmp
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Dear Bob Phillips
The solution you have given works upto only 7 digits. HOw to make
it generic or upto 11 digits.

http://www.microsoft.com/office/com...a-d523ea279588&dg=microsoft.public.excel.misc