Number formatting

id4xl

New Member
Joined
Jan 7, 2006
Messages
6
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,532
How about a custom number format of :
Code:
[>9999999.99]##\,##\,##\,##0.00;[>99999.99]##\,##\,##0.00;##,##0.00

that will cover you up to 999999999.99
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

New Member
Joined
Jan 7, 2006
Messages
6
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,532
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

New Member
Joined
Jan 7, 2006
Messages
6
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,532
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,990
Messages
5,599,226
Members
414,297
Latest member
dalkarl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top