Excel spreadsheet - export to fixed width colun file?

[ronaldmacdonald]

Hello

I have an excel (xls) spreadsheet with 4 columns:

ColA ColB ColC ColD
Hello World This Is

Is there a way to convert this to a fixed width column file (doesn't need an xls extension), so for instance:

ColA (2 chars) ColB (10 chars) ColC (5 chars) ColD (4 chars)
He World This Is

Ideally where the actual values are less than the specified column width these would be right padded with spaces.

Thanks

Ronald

 

[ClulessChris]

Text to Columns would be one way to go (Data > Text to Columns...)

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[ronaldmacdonald]

Hello CluelessChris

Yes this feature is interesting but very labour intensive.
It only lets you modify 1 column at a time and the file remains xls.
I wanted to basically produce a file that I would later be able to open in notepad and the fields be
the specified column widths.
Thanks for the response though!

 

[SkipVought]

Hi,

No VBA.

just SaveAs a .prn text file

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ronaldmacdonald]

Hi SkipVought, yes a prn file format is ok, however, I cannot (or at least did no see any option) change
the column widths. I really wouldn't want to do this manually

Ronald

 

[ClulessChris]

Err... you can do multi columns with Text to Columns. Then SaveAs the desired file type.


Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[ronaldmacdonald]

Hi CluelessChris

Not in my Excel you can't (2010).
Select multiple columns = Microsoft Excel can convert only one column as a time..."
Regardless the manual processing is a bit old school!

Ronald

 

[SkipVought]

however, I cannot (or at least did no see any option) change
the column widths. I really wouldn't want to do this manually

Build a table of column widths.

Set the column widths according to your specification.

SaveAs a .prn

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[remeng]

If the columns are in the same location for each file, why not just create a small macro to do it? All you would need to do is press a button. I've done it before and it works well.

Just my two cents...