How do you fix the length of Excel cells? 1

[everest]

I have this excel file that needs to be formatted for some old fortran program. In excel, it looks something like this:

Site Atim Zo VS
----- ----- ----- ------
Main St. 60.00 0.00 0.00
1st St. 20.00 0.00 0.00
Washington 80.00 10.00 10.00

The old fortran program requires each column width to be a certain length. For example, the Site column shown above needs to be exactly 40 characters long including spaces.

The Length(Len) of the word "Main St." is 8 and I would need to add 32 more spaces to get a total of 40 characters.

I was thinking about applying a custom format to the cells to add extra spaces at the end of the text and then using a LEFT(Site, 40) function to get the 40 characters, but that would be really cumbersome because I would have to create extra sheets.

The example table shown above is supposed to draw information from other sheets so I was thinking that a vba function would be the easiest way to approach this. But, I've learned many times from posting in this forum that vba isn't always the best/easiest way to do things, so checking to see if anyone has any other suggestions?

Thanks in advance for your help.

Everest

 

[ETID]

This is one way...

on a new sheet...in cell a1 type

=Sheet1!A1&REPT(" ",40-LEN(Sheet1!A1))

fill down

then for each column fill right, changing 40 to the field length needed for each column.