How to change digit grouping in Office 2003 ?

[hemant_mathur]

The default digit grouping in currency or accounting format is like Rs 123,456,789.00. Now i want to change it to Rs 12,34,56,789.00. I have changed it to the desired format in windows regional options but the change is not effected in excel. Please tell me how to do it ?

[Choto Cheeta]

hmm...

windows regional options but the change is not effected in excel.

it should have but strangely enough it didnt effect in my system too... !! how ever I am cheching settings !!

Ceratainly thats the way to do it, Refer to these 2 threads,

Create or delete a custom number format (http://office.microsoft.com/en-us/excel/HP051995001033.aspx)

and

Note By default, Excel displays the system separator for thousands. You can specify a different system separator by changing the regional settings in Control Panel. Or you can temporarily substitute a different character for the system separator. For more information, see the See Also section of this topic, which is visible when you are connected to the Internet.

Source (http://office.microsoft.com/en-us/excel/HP052416671033.aspx?pid=CH010036911033)

Now it refers to, here at, Change the default country/region (http://office.microsoft.com/en-us/excel/HP052427021033.aspx?pid=CH062528541033)

my one too refuses to change... !! I am still looking for, misstake if any which i have made...

!!

:(

[hemant_mathur]

Any other way to do it ?? .. it just wont accept the regional options settings.

[Pathik]

r u using xp or 98??

[hemant_mathur]

Win XP.

[Choto Cheeta]

it just wont accept the regional options settings.

Its the only easy way, there might and can be a registry twick !! let me check.. !!

P.S. I am stilk discussioning the matter in MS private news group :p no good solution yet !! I have an eye on it... will post when any solution comes up !!

[hemant_mathur]

It should get changed by the regional options .. every other option does. Still thanx for the help and hoping to get a solution soon.

[Choto Cheeta]

OK... Found a makeshift solution...

Its a solution by our co-MPV Dr. U.B. Pavanaja, select the cell or cells in excel, now go to Format Cell, and then option Custom under Number Catagory !! :)

Here, enter this custom formating vallue... !!

##\,##\,##\,##0.00

http://img145.imageshack.us/img145/4852/excelne3.jpg

Now it should re-format the digit in this style, 12,34,56,789.00 but its a make shift solution as if you have digit bigger than that, you will have to do increase the ##\, according to your needs.. !!

How ever its not any offcial reply from MS :p

Post back if any more problem !!

[hemant_mathur]

Thanx for reply. It's good but this won't solve my problem as my data set is huge with variable number length and it's impossible to change it for every cell.
Still thanx for the reply and hoping to get a permanent solution soon.

[navjotjsingh]

As such there is no permanent solution to this. This is a bug in Office 2003 only. Its neither in office xp and not in Office 2007 as well. Till the time Microsoft releases a patch for it nothing can be done much about it except for the solution posted by saurav. You can increase the size of # to very large(use copy paste and make it big) so that any large no. gets formatted easily.

[hemant_mathur]

Hmm time to revert back to Office XP.
If I increase the size then extra comma (,) come before the number.