Results 1 to 10 of 10
  1. #1
    New Lounger
    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.

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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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?

  4. #4
    New Lounger
    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
    Attached Files Attached Files

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

    Cheers
    Attached Files Attached Files
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

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

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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •