Save As Fixed Width Fields

  • Thread starter Thread starter Brenda Hutton
  • Start date Start date
B

Brenda Hutton

Excel 2000, SR-1

Customer wants to save spreadsheet as fixed width fields to import into a
Mainframe. I can't think how you'd do that. I suspect it may involved
merging into a Word document, but I don't have a handle on that either.
Anyone have any ideas? TIA!
 
You have a few choices (try against a copy of your worksheet):

You might be able to change the font to Courier New (a fixed width font) and
then do File|SaveAs, choose "Formatted Text (space delimited) (*.prn)" (But
that's limited to 240 characters per line--may not be important to you now.)

But I've always struggled with making the columns fit nicely.

I'd either concatenate the cell values into another column:

=LEFT(A1&REPT(" ",20),20) & LEFT(B1&REPT(" ",10),10) & TEXT(C1,"000,000.00")

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]

(in fact, it contains most of this message!)
 
Dave, thank you for the formula. Unfortunately, I do not understand at all
what this formula is doing. Can you give me a quickie lesson on that? I
have looked up REPT (repeats the data in the referenced cell) and LEFT
(which makes no sense to me at all) in Help but I don't understand what this
is doing.
 
Say you have Brenda in column A and Hutton in column B and a date in column C
and an amount in column D.

Brenda Hutton July 30, 2003 $1,234.45

But column A has to be 15 characters.
B has to be 20 characters
C has to be formatted as yyyymmdd (8 characters)
D has to be 20 characters right justified with just the decimal
point (no $, no commas).
And say there has to be a space between fields.

If I just did =a1&b1&c1&d1, you'd get something like this:
BrendaHutton378321234.45

But you could pad with extra spaces:

=a1&" "
Which would return "brenda " (w/o the quotes)

Well, I wouldn't want to take the time to pad each name with spaces. If the
list is more than 3, I'd screw it up!

so you could write:
=left(a1&" ",15)
and you'd get
"brenda "

=left() just means to take that number of characters (15) from that string from
the Left side.

In our case, the string was brenda followed by " " (fifteen
spaces).

And that gets boring to count those spaces, too.
But excel has a nice way to repeat a bunch of characters.

=rept("x",12) will return 12 x's. "xxxxxxxxxxxx" (w/o the quotes).

So to get "brenda " (brenda and 9 spaces), I could write:

=left(a1&rept(" ",15),15)

To pad the last name to 20, I'd go through all that rigmarole to get this
formula:
=left(b1&rept(" ",20),20)

And to put them together to make one field (and separated by a space), I could
string them together.

=left(a1&rept(" ",15),15) & " " & left(b1&rept(" ",20),20)

=====
Now comes the date stuff. If I just concatenate the date, I get some funny
looking number (the number of days since Jan 1, 1900 (for most windows users).

So I have to format it nicely:

=text(c1,"yyyymmdd")
extending my string, I'd get this:

=left(a1&rept(" ",15),15) & " " & left(b1&rept(" ",20),20)
& " " & text(c1,"yyyymmdd")

(all one cell when you enter the formula)

same kind of thing for the money field. I include the format like:

=text(d1,"0.00")

But in this case, I want the number right justified. So I pad on the other
side:

=right(rept(" ",20)&text(d1,"0.00"),20)

Then I just add it to my big ole formula (and include a space)

=left(a1&rept(" ",15),15) & " " & left(b1&rept(" ",20),20)
& " " & text(c1,"yyyymmdd") & " " & right(rept(" ",20)&text(d1,"0.00"),20)

(Still one line).

So this formula says take the value in A1 and pad on the right to get 15
characters. Add a space.

Then take the value in B1 and pad on the right to get 20 characters. And add a
space.

Format the value in C1 and format it as yyyymmdd (didn't have to pad at all).
And add a space.

Then take the value in D1 and pad on the left to get a string of 20 characters.
But don't include any $ or commas.

put it all together and the formula evaluates to:
Brenda Hutton 20030730 1234.45

Drag this formula down the column.
Copy that range and paste it into notepad and save it as *.txt.

The formula may look ugly when it's built, but each individual piece isn't too
bad.

Format that column using a font of Courier New and you'll be able to see how
things line up.

Add a formula like this to a header cell at the top of that helper column:

=rept("----+----|",12)
(format it as courier new, too

And you'll get a cell with something like this in it:
----+----|----+----|----+----|----+----|
That'll help you line up columns.

This may seem like a pain, but when you do it once or five times, it won't be so
bad.

And hide that column when you're done--so you don't ever have to build it from
scratch again.

That's kind of what that macro tried to do. Pad a bunch of characters and
truncate to just the important length.

You may find that you have better luck changing the font to courier new and
adjusting the columnwidth. Then saving as a .prn (formatted text) file.
 
Back
Top