Remember Me?

#1
September 19th 07, 06:04 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2007 Posts: 2
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
September 19th 07, 06:27 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 3,268
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
September 19th 07, 06:48 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2007 Posts: 638
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
September 20th 07, 02:50 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: May 2007 Posts: 2,202
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?

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Julieeeee Excel Worksheet Functions 6 November 15th 06 05:55 PM dantee Excel Worksheet Functions 8 March 30th 06 11:05 PM Curalice Excel Worksheet Functions 2 January 18th 06 09:49 PM monkeyforce5 Excel Discussion (Misc queries) 4 September 9th 05 09:49 PM frank Excel Discussion (Misc queries) 4 July 12th 05 02:31 AM

All times are GMT +1. The time now is 09:40 PM.