Padding Cells with Zeros and Spaces (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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 Rept 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

Reply | Quote

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

=TEXT(A1*100,"0000000000")

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.

Reply | Quote

Joanne, I think the formula that you need is

 =IF(ISNUMBER(A1),TEXT(A1*100,"0000000000"),
     IF(LEN(A1)<10,CONCATENATE(A1,REPT(" ",10-LEN(A1))),LEFT(A1,10)))

That will take care of everything

Reply | Quote

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

Joanne

Reply | Quote

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

Reply | Quote

Now you can rewrite that legacy code that wanted the data that way! Let me guess, it’s written it RPG, right? –Sam

Reply | Quote