Hi,

I need to export a spreadsheet to a fixed width text file. I've seen
people suggest saving spreadsheet to .prm file. That doesn't work for
me because one of the column of my spreadsheet has 450 characters.
That's way over prm file can support. Here is the layout of my
spreadsheet.

column 1, text, 8 chars
column 2, text, 30 chars
column 3, text, 50 chars
column 4, text, 450 chars

Any ideas. I'd appreciate any help.

Bing

You could build your own formula and pad it with spaces:

=LEFT(A1&REPT(" ",8),8)
&LEFT(B1&REPT(" ",30),30)
&LEFT(C1&REPT(" ",50),50)
&LEFT(D1&REPT(" ",450),450)

Then copy this formula down the column as far as you need.

Then copy that range to NotePad and save from there.

=============
Saved from a previous post:

There's a limit of 240 characters per line when you save as .prn files. So if
your data wouldn't create a record that was longer than 240 characters, you can
save the file as .prn.

I like to use a fixed width font (courier new) and adjust the column widths
manually. But this can take a while to get it perfect. (Save it, check the
output in a text editor, back to excel, adjust, save, and recheck in that text
editor. Lather, rinse, and repeat!)

Alternatively, you could 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.)

Drag it down the column to get all that fixed width stuff.

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/groups?threadm=015...0a% 40phx.gbl

" wrote:

Hi,

I need to export a spreadsheet to a fixed width text file. I've seen
people suggest saving spreadsheet to .prm file. That doesn't work for
me because one of the column of my spreadsheet has 450 characters.
That's way over prm file can support. Here is the layout of my
spreadsheet.

column 1, text, 8 chars
column 2, text, 30 chars
column 3, text, 50 chars
column 4, text, 450 chars

Any ideas. I'd appreciate any help.

Bing

--

Dave Peterson

On Feb 17, 10:40*am, Dave Peterson wrote:
You could build your own formula and pad it with spaces:

=LEFT(A1&REPT(" ",8),8)
&LEFT(B1&REPT(" ",30),30)
&LEFT(C1&REPT(" ",50),50)
&LEFT(D1&REPT(" ",450),450)

Then copy this formula down the column as far as you need.

Then copy that range to NotePad and save from there.

=============
Saved from a previous post:

There's a limit of 240 characters per line when you save as .prn files. *So if
your data wouldn't create a record that was longer than 240 characters, you can
save the file as .prn.

I like to use a fixed width font (courier new) and adjust the column widths
manually. *But this can take a while to get it perfect. *(Save it, check the
output in a text editor, back to excel, adjust, save, and recheck in that text
editor. *Lather, rinse, and repeat!)

Alternatively, you could 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.)

Drag it down the column to get all that fixed width stuff.

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/groups?threadm=015...24a501280a% 4...



" wrote:

Hi,

I need to export a spreadsheet to a fixed width text file. *I've seen
people suggest saving spreadsheet to .prm file. *That doesn't work for
me because one of the column of my spreadsheet has 450 characters.
That's way over prm file can support. *Here is the layout of my
spreadsheet.

column 1, text, 8 chars
column 2, text, 30 chars
column 3, text, 50 chars
column 4, text, 450 chars

Any ideas. *I'd appreciate any help.

Bing

--

Dave Peterson

Thanks very much for your quick response. I'm not good at Excel. The
formulas you gave works for one cell. I'm trying to figure out how to
apply that formulas to the whole column.

Bing

On Feb 17, 3:32*pm, " wrote:
On Feb 17, 10:40*am, Dave Peterson wrote:



You could build your own formula and pad it with spaces:

=LEFT(A1&REPT(" ",8),8)
&LEFT(B1&REPT(" ",30),30)
&LEFT(C1&REPT(" ",50),50)
&LEFT(D1&REPT(" ",450),450)

Then copy this formula down the column as far as you need.

Then copy that range to NotePad and save from there.

=============
Saved from a previous post:

There's a limit of 240 characters per line when you save as .prn files. *So if
your data wouldn't create a record that was longer than 240 characters, you can
save the file as .prn.

I like to use a fixed width font (courier new) and adjust the column widths
manually. *But this can take a while to get it perfect. *(Save it, check the
output in a text editor, back to excel, adjust, save, and recheck in that text
editor. *Lather, rinse, and repeat!)

Alternatively, you could 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.)

Drag it down the column to get all that fixed width stuff.

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/groups?threadm=015...24a501280a% 4...

" wrote:

Hi,

I need to export a spreadsheet to a fixed width text file. *I've seen
people suggest saving spreadsheet to .prm file. *That doesn't work for
me because one of the column of my spreadsheet has 450 characters.
That's way over prm file can support. *Here is the layout of my
spreadsheet.

column 1, text, 8 chars
column 2, text, 30 chars
column 3, text, 50 chars
column 4, text, 450 chars

Any ideas. *I'd appreciate any help.

Bing

--

Dave Peterson

Thanks very much for your quick response. *I'm not good at Excel. *The
formulas you gave works for one cell. *I'm trying to figure out how to
apply that formulas to the whole column.

Bing

Never mind. I figured out how to drag the formulas to apply to the
whole column. That worked like a charm. Many thanks again!

Bing

You can copy the formula down the column.

Debra Dalgleish explains how to fill a range:
http://contextures.com/xlDataEntry01.html

" wrote:
<<snipped

Thanks very much for your quick response. I'm not good at Excel. The
formulas you gave works for one cell. I'm trying to figure out how to
apply that formulas to the whole column.

Bing

--

Dave Peterson