Custom Number Format

  • Thread starter Thread starter Donna Newton
  • Start date Start date
D

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