Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Oct 2001
    Location
    Canada
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Padding Cells with Zeros and Spaces (Excel 2000)

    We need to create a text file from an existing spreadsheet so that all of the rows (records) are a fixed length and the text file cannot have any delimiters. How can I 'right-justify and pad with zeros' a column of currency without showing the decimal point? My second question is: How do I left-justify and pad with spaces other fields such as names (this is a requirement)? Do I need to put non-breaking spaces in each field to fill the column to the desired length? Some of the data has to be entered into the spreadsheet yet. Is it better to format the cells to validate entered text or to format the columns after all information is entered? In short, does Excel prepare files for archaic requirements such as these? Any thoughts or ideas would be greatly appreciated.

    Joanne

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Padding Cells with Zeros and Spaces (Excel 2000)

    Joanne

    for your padding of zeros you can use a formula such as : =CONCATENATE(REPT(0,10-LEN(I1)),I1)

    Now this will pad enough zeros to make the length 10. You can adjust that to your needs.

    For the spaces well substitute what the <font color=red> Rept </font color=red> function will use. The left and right justify should be controled by the Format of the cell but make sure that the column is a bit wider than the needed width.

    HTH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

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

    Re: Padding Cells with Zeros and Spaces (Excel 2000)

    The formula below will convert a currency value to text with ten digits with leading zeros and no decimal point.

    <pre>=TEXT(A1*100,"0000000000")
    </pre>


    You can either create a new worksheet using formula like that one and save that sheet to a DOS text file, or write a macro to create the file for you.
    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Padding Cells with Zeros and Spaces (Excel 2000)

    Joanne, I think the formula that you need is
    <pre> =IF(ISNUMBER(A1),TEXT(A1*100,"0000000000"),
    IF(LEN(A1)<10,CONCATENATE(A1,REPT(" ",10-LEN(A1))),LEFT(A1,10)))</pre>

    That will take care of everything
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Lounger
    Join Date
    Oct 2001
    Location
    Canada
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Padding Cells with Zeros and Spaces (Excel 2000)

    Thank you for all of your help. I will try these suggestions but it sounds like exactly what I needed.

    Joanne

  6. #6
    Lounger
    Join Date
    Oct 2001
    Location
    Canada
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Padding Cells with Zeros and Spaces (Excel 2000)

    A big thank you to SammyB., Legare and Wassim. My file is created. I learned a great deal from the 3 of you and appreciate the time. The concatenate tip was the icing on the cake.

    Joanne

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Padding Cells with Zeros and Spaces (Excel 2000)

    Now you can rewrite that legacy code that wanted the data that way! Let me guess, it's written it RPG, right? <img src=/S/eargear.gif border=0 alt=eargear width=20 height=20> --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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