How do I export an Excel spreadsheet with fixed length rows?

[Guest]

I want to create a file with fixed length records (lines). The fields
(columns) have defined lengths. When I export the file, it should be in a
flat text format, there should be no spacing between columns of data, and the
total line length should be exactly what I want.

How do I do this? What format should I export the data into?

 

[Jim Rech]

You should try the "Formatted Text (Space delimited) (*.prn)" format. You
must adjust the worksheet column widths to the fixed field widths you want
before the file, save. Unless you use a fixed width font like Courier New
this (the column widths) may not look right in the worksheet, but the text
file should be okay.

 

[Earl Kiosterud]

It's probably worth mentioning that setting the column width should be done
with Format - Columm - Width. If you set the widths visually, it's pretty
much mandatory that you use a fixed-pitch font like Courier, which seems to
correlate closely with the actual width of the columns, in characters. The
decimal digits have the same widths in many fonts, but other characters
don't.

 

[Guest]

Thanks for your replys, Jim and Earl,

The part I was missing was the Formatted Text (Space delimited) export
format. I am planning to use the Data > Validation > Text length tool to make
sure each field is the right length, and I will use Courier New font.

Paul

 

[JE McGimpsey]

If you'd rather not reformat your worksheet to accommodate the png
format, you can use VBA to export fixed field formats:

http://www.mcgimpsey.com/excel/textfiles.html#fixedfield

 

[Guest]

Thanks John, I will give this a try.

Paul

If you'd rather not reformat your worksheet to accommodate the png
format, you can use VBA to export fixed field formats:

http://www.mcgimpsey.com/excel/textfiles.html#fixedfield