how can I format output data to a file

[bob wallis]

How can I format data for output to a file in a particular
format ie. 6 chars 25 chars 49 chars from 3 work sheet
columns which a not 2,25 and 49 long. This is so that the
data can be read by another program. If the first cell
only has a "1" in it how do I get excel to output blanks
to fill the space to 6 char for the first output?

 

[Jim Rech]

Set your column widths to the field widths you want (6, 25, 49). Set the
text alignment for each column to what you want if the default (left for
text and right for numbers) is not correct.

Then do a File, Save As and pick Formatted Text as the file format.

This will do pretty much what you want. The last field may be a problem
since, if the text is left-aligned, Excel will not fill it out with spaces.
If this is a showstopper you might consider adding a dummy field at the
right of the list you're saving. Perhaps the reading program will ignore
it.

 

[Dave Peterson]

Another option is to use a helper column:

=LEFT(A1&REPT(" ",6),6) & LEFT(B1&REPT(" ",25),25) & LEFT(C1&REPT(" ",49),49)
(and drag down)

I like to format that helper column as Courier New so I can see how things line
up.

When I'm satisfied with it, I'll copy and paste into Notepad and save it as a
..txt file there.

Depending on what you're doing, you may want things right justified:

=right(rept(" ",6),a1),6 & .....

And if you have numbers/dates/money that you want formatted, you may want:

=....&LEFT(TEXT(B1,"mm/dd/yy")&REPT(" ",25),25) & ....