Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    adding zeros and a comma (Excel 2002)

    Hi

    I need to add 10 zeros to the front of a number and a comma at the end ie 6000460 to become 00000000006000460, I tried concatenation but it removed all the zeros.
    Can any one help please


    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: adding zeros and a comma (Excel 2002)

    Format the cell(s) as text before modifying the value.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: adding zeros and a comma (Excel 2002)

    Hi Hans

    I did what you suggest format the cells a text and then a custom format adding the 10 zeros but when I try to concatenate with the comma it removes the leading zeros. I have to apply this to hundreds of rows.

    Thanks for your reply

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: adding zeros and a comma (Excel 2002)

    If you apply a custom format, you overrule the text format. If you would like to use a custom format, use <code>00000000000000000,</code>
    Remember, a custom format only changes the way a value is displayed, it doesn't alter the way the value is stored. Depending on what you want to accomplish, this may or may not be what you need.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: adding zeros and a comma (Excel 2002)

    Hi Hans

    I tried custom format as you suggested but I cannot get the comma to appear. the zeros yes but the comma no.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: adding zeros and a comma (Excel 2002)

    Braddy,

    Try the formula noted in Cell B1. Providing each of your accounts have a length of 7 then all you have to do is repeat a ZERO (17-7 or 10) times and then concatenate a comma.

    John

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: adding zeros and a comma (Excel 2002)

    Hans' custom format was almost correct. Try 00000000000000000"," as a custom format.
    Legare Coleman

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: adding zeros and a comma (Excel 2002)

    Hi Legare

    Thanks very much it works just fine , Thanks to Jstevens for for your reply too,

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: adding zeros and a comma (Excel 2002)

    Thanks - the reason it worked for me without the quotes was that my system settings use the comma as decimal separator.

Posting Permissions

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