How to save excel file to fixed-length format

[Johnny Rubbles]

Hi,

I have an Excel worksheet of 5,000+ records that i need to save to a
fixed-length ascii file. I have tried to search the excel help file
but I could not seem to find the answer. I know this could be done in
Excel 2002. Anybody has the solution or way to do this?

Thanks.

Johnny

- Johnny Rubbles
(e-mail address removed)

 

[Dave Peterson]

You have a few choices (try against a copy of your worksheet):

I'd either concatenate the cell values into another column:

=LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00")

(You'll have to modify it to match what you want.)

Then I'd copy and paste to notepad and save from there. Once I figured out that
ugly formula, I kept it and just unhide that column when I wanted to export the
data.

If that doesn't work for you, maybe you could do it with a macro.

Here's a link that provides a macro:
http://google.com/[email protected]

 

[Johnny Rubbles]

Thanks Dave. I got your idea. I think I should be able to create the
fixed-length file from here.

Johnny

 

[Stephen Bye]

When saving the file, in the 'save as type' drop down box, select "Formatted
text (space delimited)". This will pad the data with spaces where necessary
to match the column widths that you have defined for the cells.