Comma Formatting

[ramchu2002]

i work for a indian company and they prefer numbers to be formatted as below :

1,23,45,678.

if i use excel format options it puts a comma every 3 digits wheras what i want is to the left of decimal point first comma should be after 3 digits and all other commas should be after 2 decimals.

help please.

 

[Yogi Anand]

On 2002-08-22 20:39, ramchu2002 wrote:
i work for a indian company and they prefer numbers to be formatted as below :

1,23,45,678.

if i use excel format options it puts a comma every 3 digits wheras what i want is to the left of decimal point first comma should be after 3 digits and all other commas should be after 2 decimals.

help please.

Hi ramchu2002:

You could custom format the 8 digit number as: 0","00","00","000

to be displayed as 1,23,45,678


The comma formatting is intended as a separator for thousands. If you are looking for a comprehensive solution that may require a VBA sub/function. For your particular case one of the ways would also be to use the following formula to convert the number into a text string with commas at appropriate locations:

=IF(LEN(B2)=8,REPLACE(B2,2,7,","&MID(B2,2,2))&","&MID(B2,4,2)&","&MID(B2,6,3))

see the worksheet simulation

Book1
	A	B	C	D
1
2		12345678	1,23,45,678
3
Sheet6

</SPAN>

I recognize that it is limited to the 8-digit number ... but it will get the ball rolling.

Regards!

Yogi
This message was edited by Yogi Anand on 2002-08-23 09:03

 

[dtaylor]

or you could just setup a custom number format
something like this ##,###,###

 

[PaddyD]

Ramchu2002,

There is an option for this in your regional settings.

Start | Settings | Control Panel | Regional Options | Number tab.

You should have 12,34,56,789 in the 'digit grouping' options. There is an equivalent option in the currency tab.

Paddy
This message was edited by PaddyD on 2002-09-02 19:18

 

[Yogi Anand]

On 2002-09-02 19:15, PaddyD wrote:
Ramchu2002,

There is an option for this in your regional settings.

Start | Settings | Control Panel | Regional Options | Number tab.

You should have 12,34,56,789 in the 'digit grouping' options. There is an equivalent option in the currency tab.

Paddy
This message was edited by PaddyD on 2002-09-02 19:18

Hi Paddy:

The OP's number is 12345678 and it is to be formatted as 1,23,45,678 (in India, you measure larger numbers in thousands, then 100s of thousands (Lakhs), then 100s of lakhs (crores), and so on -- that is why, the commas for thousands, 100 thousands(lakhs), crores, etc.

I believe the regional settings only give groupings for either 3, or, 2, ... etc exclusively, but not mixed as 3, for the first thousands, and then 100s after that) -- if you know it to be differently dispositioned, please advise.

Regards!

Yogi

 

[PaddyD]

Hi Yogi! Think it must be an OS version issue. I checked that on the work PC (OS = wndows 2000) & the digit grouping options was definitely there (I think!!). Checked on home PC (win 98), & the digit grouping option is just a drop down with 0,1,2 etc - what are you running?

Paddy