Number formatting

[id4xl]

Hi

I have a number :

35678329

I want it to appear in xl as below

3,56,78,329

meaning 3 crores 56 lakhs 78 thousands 3 hundred and 29.

We have crores and lakhs in india.. and are comfortable with that than millions and billions..

how do i format the cell to display the commas like this?

i tried changing the # formatting through control panel - regional setting - standards & formats (English UK) - customise - and selected 12,34,56,789

still doesn't work

pl dont tell me i need to get used to millions and bns ...........help !!!!!!!!!!!

 

[GlennUK]

How about a custom number format of :

[>9999999.99]##\,##\,##\,##0.00;[>99999.99]##\,##\,##0.00;##,##0.00

that will cover you up to 999999999.99

 

[lenze]

Dear RAM / LENZE,

Thanks, that code worked, but yes only in column A.
Guess this is better than nothing at all.

The code can easily be modified for any range or column(s). It's simply a matter of defining the target range. Where do you want the code to apply?

lenze

 

[id4xl]

Dear GlennUK,

FANTASTIC.. THANKS.. IT WORKS...

Now if I am not asking for too much, is there a way to save this format in XL as one of the defaults or everytime I have to copy this onto
Format - Cells - Custom.


Great job.. thanks again..

 

[GlennUK]

There are a few ways to do this ... create an Excel book template with the number format in it, or create a sheet template, so that you can add a new sheet to a book and it will bring in the format.

Or you could add it to your Personal.xls, and store it in a named style, and when you need it elsewhere, unhide your Personal.xls, switch back to your target workbook, and do Format/Style/Merge Styles and merge from your Personal.xls workbook ( merging style brings in any number formats used in those styles ).

Or you could write a macro, stored in your Personal.xls, and have that macro create a custom number format, and have that macro attached to a toolbar button.

 

[id4xl]

oops, I forgot to mention that I am a layman.. macros and templates sound too technical for me..

nevertheless, my sincere thanks for your support. it helped a great deal.

 

[GlennUK]

my pleasure.

If you are a layman, I'd recommend just having a special workbook with your formats in ... and do Format/Style to create a named style for each format. That way you can open your formats book and use the Format/Styles/Merge Styles command to get your formats copied in. That doesn't need any macros or messing with templates.