save an excel file in fixed length records whose fields are blank

[Guest]

I need to save the data in ascii format including fields that are fixed
length and have spaces in them. I have tried every save as type. Each column
is formatted to the column width of that field (10cpi). For example a last
name may be smith but that field is 15 characters long.Excel puts either a
space or tab between fields but does not retain the spaces. please help

 

[Dave Peterson]

First, I'd change the font to a fixed width font--I use Courier New.

Then widen each column to what you want and save that file as a:
Formatted Text (Space delimited)(*.prn)

But those files have a 240 character per line limit.

Saved from a previous post:

There's a limit of 240 characters per line when you save as .prn files.

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]