fixed format export

[John Wilson]

Is it possible (minus macro coding) to export a simple multi-column
excel (single sheet) worksheet in a fixed format? Somewhere you would
need to define that column A should be characters 1-2, column B
should be 3-12 and so on.

Thanks,

jpw


ps. - I can solve this problem other ways (vbscript, etc) but I was
looking for a simpler method.

 

[Jim Rech]

Set each column's width to what you want in the text file (3 or 6, etc.).
Then do a File, save As and pick "Formatted Text (Space delimited)(*.prn)"
as the file format.


--
Jim Rech
Excel MVP

| Is it possible (minus macro coding) to export a simple multi-column
| excel (single sheet) worksheet in a fixed format? Somewhere you would
| need to define that column A should be characters 1-2, column B
| should be 3-12 and so on.
|
| Thanks,
|
| jpw
|
|
| ps. - I can solve this problem other ways (vbscript, etc) but I was
| looking for a simpler method.

 

[Dave Peterson]

I found that when I use Jim's technique, it goes a little smoother if I choose a
nice non-proportional font (I use Courier New) so I can see how things look).

But another way is to use a helper cell where you concatenate your values.

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

Then copy that column and paste into notepad and save the text file from there.

I also add a cell like this near the top of that column:
=REPT("----+----|",10)
And format the whole column as Courier New

(If I have to do it lots, I'll leave the formulas and hide the column!)