excel formatting to text file

[paul]

hello

the problems is i have to save an excel file to text file,
so that column A takes up the first 10 columns,
column B takes up 7 columns in the text file and so on ,

or word it in another way

each of the excel columns have to be placed at certain columns of the text
file
column a in excel takes up spaces 1to 7 in the text file
column b in excel takes up 8-12 in the text file
etc

how would i go about doing this in excel 2003
thanks paul!

 

[Dave Peterson]

Save your workbook first!

One way is to save the file as a .prn file. I'd change the Font to Courier New
so that you can widen the columns and how things look (and inspect the text file
using Notepad to double check the .prn file).

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

One workaround...

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]

=========

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[paul]

thats dave
that worked perfectly

all the best

Save your workbook first!

One way is to save the file as a .prn file. I'd change the Font to Courier New
so that you can widen the columns and how things look (and inspect the text file
using Notepad to double check the .prn file).

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

One workaround...

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]

=========

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[paul]

thanks dave

that worked perfectly
all the best

Save your workbook first!

One way is to save the file as a .prn file. I'd change the Font to Courier New
so that you can widen the columns and how things look (and inspect the text file
using Notepad to double check the .prn file).

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

One workaround...

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]

=========

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[David McRitchie]

Hi Paul,
Don't know if you will get exactly what you want but
would suggest making a copy of the worksheet and using a fixed font
and adjusting your column widths (Excel columns).

Just a tad hard to read when you use the word columns, since Excel
has Columns. Positions or "fixed width columns for export" would
be easier to read.