how to export a spreadsheet with column width over 255 maximum excelallows?

[du_bing]

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]

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/[email protected]

 

[du_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 thattext
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 figuredout 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=015b01c32c5f$b3d398d0$a501280a%4...

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

 

[du_bing]

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

 

[Dave Peterson]

You can copy the formula down the column.

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

: