Comma Formatting

ramchu2002

New Member
Joined
Aug 19, 2002
Messages
9
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
ABCD
1
2123456781,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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,196,368
Messages
6,014,854
Members
441,850
Latest member
peh16

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
Back
Top