# Thread: Make a decimal number show all zeros (2003)

1. ## 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.

2. ## 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.

3. ## 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?

4. ## 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

5. ## 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

Cheers

6. ## 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>

7. ## 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>

8. ## 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

9. ## 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

10. ## Re: Make a decimal number show all zeros (2003)

Glad you found it yourself.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•