Fixed Field Length Issue

  • Thread starter Thread starter PatRyan
  • Start date Start date
P

PatRyan

We have an Excel spreadsheet that needs to be saved as a text file to be
picked up by another application. The problem is when we save it as a text
file, additional spaces are added in between the data in Col A and Col B,
etc. even though we've defined the length for each column. Help would be
greatly appreciated....
Thanks!
 
PatRyan said:
We have an Excel spreadsheet that needs to be saved as a text file to be
picked up by another application. The problem is when we save it as a
text
file, additional spaces are added in between the data in Col A and Col B,
etc. even though we've defined the length for each column. Help would be
greatly appreciated....
Thanks!
Click to expand...

I've never been able to produce good transfer files of the type you
describe. In case nobody has a better solution, you can import your Excel
file into an Access table and then export it to a text file. In this way you
can set the format of each column and define exactly how may characters wide
it should be.

Most of the Excel txt files are tab or comma delimited, from your
description it looks like you may have selected the .prn option which would
use spaces rather than tabs. It is not easy to control the exact column
width using this format and the last column truncates at the length of the
longest string.

It's a shame that Excel can't take the validation information and pad all
the cells to the right length when exporting to text files.

Regards

Thomas
 
Thanks Thomas...the access database approach does seem to work for the most
part. I probably shouldn't have marked this post as answered in case
someone else does know of a solution within Excel. Is there a way to take
away the "Answer" notation?
Thanks!
 
Pat,

Here's how to do it in Excel (limited by the maximum number of characters
Excel allows in one cell):

1. Keep row 1 blank, enter your column labels in row 2 and your data below
that.

2. Copy the column titles and paste them to the right of your data.

3. Above each of the new column titles, enter the length of the field (e.g.
25 for a 25 character wide field).

4. Use one of the following formulae for each column according to whether
the column should be right or left justified:

=LEFT(A3&REPT(" ",D$1),D$1)

left justified. Data in A3, number of characters in D1.

=RIGHT(REPT(" ",F$1)&C3,F$1)

right justified. Data in C3, number of characters in F1.

You can pull these formulae to the right or downwards to calculate all of
the new values. You might need to write a new formula to process dates in
the format you require.

Concatenate all of the new columns in one new column at the end of the data
(e.g. cell G3 =D3&E3&F3).

Copy the row with the concatenated data and paste this into a Notepad
document.

Save as a text file.
 
Back
Top