Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm receiving a file with a column with alpha numerical text. I need to add to the left of the value zero's to make it 12 digits. An example is 1A becomes 0000000001A and 124ABC should be 000000124ABC. Is it possible to format the cell to show zero's for all open spaces. If not can someone help me with a macro.

    TIA

  2. #2
    Super Moderator BATcher's Avatar
    Join Date
    Feb 2008
    Location
    A cultural area in SW England
    Posts
    3,451
    Thanks
    33
    Thanked 196 Times in 176 Posts
    I found that
    Code:
    =RIGHT(CONCATENATE("00000000000",A1),12)
    seems to work ok?
    (This assumes the value in in cell A1, and the formula is in cell B1, say...)
    BATcher

    "The trouble with quotes on the internet is that you can never know if they are genuine."
    Abraham Lincoln
    

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, it works 100%

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is another method.
    =REPT("0",12-LEN(A1))&A1

    Another version of BATcher's formula in case you are like me and have a hard time spelling "concatenate".
    =RIGHT("00000000000" & A1,12)

  5. #5
    Super Moderator BATcher's Avatar
    Join Date
    Feb 2008
    Location
    A cultural area in SW England
    Posts
    3,451
    Thanks
    33
    Thanked 196 Times in 176 Posts
    It's all right for you, Mr Barron, because you know what you're doing! I extracted that from the Help -> Functions information, since I rarely use Excel...
    BATcher

    "The trouble with quotes on the internet is that you can never know if they are genuine."
    Abraham Lincoln
    

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I was merely pointing out there are other options and that I cannot spell.

Posting Permissions

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