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.