MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formatting


Posted by Manu on October 26, 2001 6:57 AM

I'd like to convert data in my field (currently in cents e.g. 1215, 1310, 4525 ....) t0 display as e.g. ($12.15, $13.10, $45.25 ...)

I realize this can be accomplished by dividing the entire field by 100 and formatting as Currency, but How Should I do it?? (my mind's failing me on a Friday..)

All help is appreciated.

Regards,


Posted by Tom Urtis on October 26, 2001 7:04 AM

Custom format your range as such: $00"."00

Posted by Mark W. on October 26, 2001 7:13 AM

Enter 100 into an unused cell. Copy it, select
the cells containing your data, and perform a
Paste Special Value using the "Divide" operation.

This is preferable to using a format such as
$00"."00 because you'll get expected results when
you perform arthmetic operations on your values.

Posted by Tom Urtis on October 26, 2001 7:21 AM

Mark's right

Mark has a good point; the format alternative is viable if your data won't be used in any other calculations, unless the cells for those other calculations are custom formatted as well, which is a pain and easy to lose track of. Thanks MW.

Tom U.