Results 1 to 10 of 10

20080125, 12:35 #1
 Join Date
 Dec 2007
 Posts
 11
 Thanks
 0
 Thanked 0 Times in 0 Posts
Make a decimal number show all zeros (2003)
I have a small aggravating problem.
I have a column of numbers from 2007 001a all the way to 2007 147a, but in the cell where I expect 2007 010a I get 2007 01a, simiarly 2007 100a gives me 2007 1a.
The end zeros get swalllowed up. I have formated the coumn number to read 3 decimal points.

20080125, 12:40 #2
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Make a decimal number show all zeros (2003)
Have you tried to make the "Number" format General. The values you typing are actually text...not numbers.
Regards,
Rudi

20080125, 12:43 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Make a decimal number show all zeros (2003)
A value such as 2007 001a is text, no number, because of the letter a at the end, so Excel will ignore the number format. Or am I missing something? Could you attach a small sample workbook?

20080126, 09:26 #4
 Join Date
 Dec 2007
 Posts
 11
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Make a decimal number show all zeros (2003)
Rudi / Hans have tried the the General and Text but with no success.
As requested enclosed is the workbook ~ also being easier to explain and see what I have done to get to this 2007 001a ( tackled it in two steps in the macro for the initial 2007.001 ). Many Thanks

20080126, 12:45 #5
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Make a decimal number show all zeros (2003)
I am glad you were able to sort out your problem.
I have viewed your sample file and made a couple of small improvements, (based on my personal opinions).
1. I added protection to the "Project entering page", so that only cells for the form can be selected. The password to unprotect is: "pass"
2. I modified some of the code to include the protection of the sheet, and deleted some of the unnecessary statements
3. One of the dynamic range names was in error. I modified the range to exclude the blank being selected
CheersRegards,
Rudi

20080126, 13:50 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Make a decimal number show all zeros (2003)
You can use this formula in B7:
<code>
=REPLACE(TEXT(C7,"0000.000"),9,1,"a")
</code>
The TEXT function converts the numeric value in C7 to a text string with 3 decimal places.
You could even use one formula to replace the two formulas in A7 and B7:
<code>
=REPLACE(REPLACE(TEXT(C7,"0000.000"),9,1,"a"),5,1, " ")
</code>
But of course that is harder to read and maintain. A slightly shorter alternative would be
<code>
=SUBSTITUTE(TEXT(C7,"0000,000"),","," ")&"a"</code>

20080126, 13:51 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Make a decimal number show all zeros (2003)
You didn't address the original question <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

20080127, 11:17 #8
 Join Date
 Dec 2007
 Posts
 11
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Make a decimal number show all zeros (2003)
Hans
I have placed the formula =REPLACE(TEXT(C7,"0000.000"),9,1,"a") as you suggested in the B7 cell and it works great ~ but how do I write it to add into the VB macro so the all the cells in B column get actioned by it when progressive numbers are generated to the sheet

20080127, 12:24 #9
 Join Date
 Dec 2007
 Posts
 11
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Make a decimal number show all zeros (2003)
Hans
Have worked out what I need to use.
It was ActiveCell.FormulaR1C1 = "=REPLACE(TEXT(R[0]C[1],""0000.000""),9,1,""a"")" it was the double " that did the trick.
Once again thankyou

20080127, 13:12 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Make a decimal number show all zeros (2003)
Glad you found it yourself.