| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to customize number to 10 digits including 2 digits after deci
I want to to display numbers into 10 digits including cents but no decimial
point ".". For example: $0.01 display as 0000000001 or $100.00 display as 0000010000. Can some one help? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to customize number to 10 digits including 2 digits after deci
Not possible using formatting
-- Regards, Peo Sjoblom "Carina" wrote in message ... I want to to display numbers into 10 digits including cents but no decimial point ".". For example: $0.01 display as 0000000001 or $100.00 display as 0000010000. Can some one help? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to customize number to 10 digits including 2 digits after deci
As Peo said, this can't be done via formatting. You can use a rather
complex formula to acheive the result though. The formula below is assuming that the current value is in cell C2: =IF(ISERROR(FIND(".",C2,1)),TEXT(C2,"00000000")&"0 0",IF(FIND(".",C2,1)+1=LEN(C2),TEXT(C2,"000000000" )&MID(C2,FIND(".",C2,1)+1,1) & "0",TEXT(SUBSTITUTE(C2,".",""),"0000000000"))) Carina wrote: I want to to display numbers into 10 digits including cents but no decimial point ".". For example: $0.01 display as 0000000001 or $100.00 display as 0000010000. Can some one help? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to customize number to 10 digits including 2 digits after deci
Your formula can be simplified. If the number will always have no more than
2 decimal places, or if it does and rounding is permitted, you can use this... =TEXT(100*A1,"0000000000") If the number can have more than 2 decimal places, but no rounding is allowed, you can do this... =TEXT(ROUNDDOWN(100*A1,0),"0000000000") Rick "JW" wrote in message ups.com... As Peo said, this can't be done via formatting. You can use a rather complex formula to acheive the result though. The formula below is assuming that the current value is in cell C2: =IF(ISERROR(FIND(".",C2,1)),TEXT(C2,"00000000")&"0 0",IF(FIND(".",C2,1)+1=LEN(C2),TEXT(C2,"000000000" )&MID(C2,FIND(".",C2,1)+1,1) & "0",TEXT(SUBSTITUTE(C2,".",""),"0000000000"))) Carina wrote: I want to to display numbers into 10 digits including cents but no decimial point ".". For example: $0.01 display as 0000000001 or $100.00 display as 0000010000. Can some one help? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count number of digits in a cell | Excel Worksheet Functions | |||
| How to return first 3 digits of a number? | Excel Worksheet Functions | |||
| adding digits of a number | Excel Worksheet Functions | |||
| How do I find the sum of all digits in a number? | Excel Discussion (Misc queries) | |||
| help with removing digits from a number | Excel Discussion (Misc queries) | |||
Hybrid Mode
