ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Numerical Grouping of 10,00,000.00 lacs not possible in Excel (https://www.excelbanter.com/excel-discussion-misc-queries/92335-numerical-grouping-10-00-000-00-lacs-not-possible-excel.html)

Utkarsh Patel

Numerical Grouping of 10,00,000.00 lacs not possible in Excel
 
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/comm...lic.excel.misc

Sattu

Numerical Grouping of 10,00,000.00 lacs not possible in Excel
 
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.




"Utkarsh Patel" wrote:

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/comm...lic.excel.misc


Bob Phillips

Numerical Grouping of 10,00,000.00 lacs not possible in Excel
 
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)

"Utkarsh Patel" <Utkarsh wrote in message
...
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/comm...lic.excel.misc



Sattu

Numerical Grouping of 10,00,000.00 lacs not possible in Excel
 
Dear Bob Phillips
The solution you have given works upto only 7 digits. HOw to make
it generic or upto 11 digits.

"Sattu" wrote:

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.




"Utkarsh Patel" wrote:

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/comm...lic.excel.misc


Bob Phillips

Numerical Grouping of 10,00,000.00 lacs not possible in Excel
 
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)

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

"Sattu" wrote:

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.




"Utkarsh Patel" wrote:

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/comm...lic.excel.misc




All times are GMT +1. The time now is 03:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com