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,421
    Thanks
    33
    Thanked 195 Times in 175 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

    Time prevents everything happening all at once...

  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,421
    Thanks
    33
    Thanked 195 Times in 175 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

    Time prevents everything happening all at once...

  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
  •