Custom 0\.00 Format with Comma

S

Sheba

I have a workbook where numbers are entered as whole numbers (includes cents
but no decimal point) on one worksheet. These numbers are pulled into
another worksheet and I need the numbers to have decimal points before the
last two numbers. From another post, I found out I can do this by formatting
these cells as Custom = 0\.00 . Is there a way to have the comma appear
before the thousands?
 
F

Fred Smith

No.

When you ask Excel to insert commas into a number, it will do so in groups
of three before the decimal point. However, Excel thinks your decimal point
is after the cent, not after the dollar. So you would end up with something
like 145,0.06, which isn't what you want.

When the numbers are "pulled into another worksheet" why don't you just
divide by 100, then format normally?

Regards,
Fred
 
F

Fred Smith

Upon further reflection, perhaps the answer is "maybe". Try the following
custom format:
[<100000]0\.00;[<100000000]#\,##0\.00;#\,###\,##0\.00

This will work for "numbers" up to 999,999,999.99

Regards,
Fred
 
S

Sheba

It worked! Thank you soooo much!

Fred Smith said:
Upon further reflection, perhaps the answer is "maybe". Try the following
custom format:
[<100000]0\.00;[<100000000]#\,##0\.00;#\,###\,##0\.00

This will work for "numbers" up to 999,999,999.99

Regards,
Fred

Fred Smith said:
No.

When you ask Excel to insert commas into a number, it will do so in groups
of three before the decimal point. However, Excel thinks your decimal
point is after the cent, not after the dollar. So you would end up with
something like 145,0.06, which isn't what you want.

When the numbers are "pulled into another worksheet" why don't you just
divide by 100, then format normally?

Regards,
Fred

.
 
F

Fred Smith

You're welcome. Thanks for the feedback.

Regards,
Fred

Sheba said:
It worked! Thank you soooo much!

Fred Smith said:
Upon further reflection, perhaps the answer is "maybe". Try the following
custom format:
[<100000]0\.00;[<100000000]#\,##0\.00;#\,###\,##0\.00

This will work for "numbers" up to 999,999,999.99

Regards,
Fred

Fred Smith said:
No.

When you ask Excel to insert commas into a number, it will do so in
groups
of three before the decimal point. However, Excel thinks your decimal
point is after the cent, not after the dollar. So you would end up with
something like 145,0.06, which isn't what you want.

When the numbers are "pulled into another worksheet" why don't you just
divide by 100, then format normally?

Regards,
Fred

I have a workbook where numbers are entered as whole numbers (includes
cents
but no decimal point) on one worksheet. These numbers are pulled into
another worksheet and I need the numbers to have decimal points before
the
last two numbers. From another post, I found out I can do this by
formatting
these cells as Custom = 0\.00 . Is there a way to have the comma
appear
before the thousands?

.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top