Custom 0\.00 Format with Comma

  • Thread starter Thread starter Sheba
  • Start date Start date
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?
 
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
 
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
 
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
Click to expand...

.
Click to expand...
 
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?
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top