Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    Glasgow, Lanarkshire, Scotland
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Blank zeros? (2002 sp1)

    Hi,

    I've managed to download some infor from our AS400 - and I'm trying to do some manipulation of the data.

    Our vendor numbers are retreived from the As400 - but I need this number padded to 5 characters e.g. vendor 72 is retreived as '72' - but I need to have it in Excel as '00072' - i.e. the 0's are significant. I can ammend the display to show me '00072' but the actual data in the cell is '72'.

    OK so I could write some code along the lines of "If LEN cell is 2, then pad answer with 000, if LEN is 3 then pad with 00 etc...." but this I think could be mucky.

    Any help appreciated.

    Regards,

    Andrew

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

    Re: Blank zeros? (2002 sp1)

    Say you have a number in A1. In another cell, put the formula

    =RIGHT("00000"&A1,5)

    This will return the value you want - as a text value, it can't be used for calculations directly,

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Blank zeros? (2002 sp1)

    You can use something like:
    =+RIGHT("00000"&A1,5)
    to convert any of the numbers, but it will convert the 72 (a value) to a string (not a value). If this is OK, you can convert all of them. Then Paste special - values to get get rid of calcs.

    If you want to keep the 72 as a VALUE but have it DISPLAY with leading zeroes, FORMAT the Cell with a custom formatno quotes):
    "00000"

    You can still use custom formatted numbers in calculations.

    Steve

  4. #4
    Lounger
    Join Date
    Apr 2001
    Location
    Glasgow, Lanarkshire, Scotland
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Blank zeros? (2002 sp1)

    Thanks guys!!

Posting Permissions

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