1. ## 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. ## 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

3. ## 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.

4. ## 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

5. ## 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. ## 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. ## 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

#### Posting Permissions

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