Custom Number Format

[Donna Newton]

I have a spreadsheet which contains currency figures which
I need to format to a 9 digit number with the decimal
places NOT rounded.

Example: 5000.50 needs to be 000500050

I can get the 9 digits; however, when I remove the decimal
point from the format, it rounds the number to 5001.

Any quick help would be greatly appreciated.

Thanks,
Donna

 

[Beth]

Two steps...

First, format the cell containing 5000.05 with custom
number format ===> FORMAT, CELLS, Select NUMBER tab,
Select CUSTOM from CATEGORY BOX. Enter 0000000.00 in TYPE
box.

Next, with value in cell A1, use this formula:
=SUBSTITUTE(TEXT(A1,"0000000.00"),".","")

Result of formula = 000500050