preparing for an ascii file

[tankgirl215]

Hello all,

I'm preparing a spreadsheet of our store inventory in product
information to be converted into an ascii file. Some of the fields
must be right aligned and zero-filled (which I've figured out using the
custom format option) and others must be left aligned and space-filled
up to a certain number of characters. Still one other type of field
must be just spaces - 120 to be exact - called filler between sections
of the file. How would I do this?

If the word I'm putting in the field is only 6 characters but the field
is supposed to have 20, how do I get the other 14 to be spaces? How
would I get just 120 spaces to fill each cell in an entire column?

Thanks!!

 

[Puppet_Sock]

I'm preparing a spreadsheet of our store inventory in product
information to be converted into an ascii file. Some of the fields
must be right aligned and zero-filled (which I've figured out using the
custom format option) and others must be left aligned and space-filled
up to a certain number of characters. Still one other type of field
must be just spaces - 120 to be exact - called filler between sections
of the file. How would I do this?

If the word I'm putting in the field is only 6 characters but the field
is supposed to have 20, how do I get the other 14 to be spaces? How
would I get just 120 spaces to fill each cell in an entire column?

I would do it this way:
- One sheet to do the actual calculations on.
- One sheet to make things pretty, filled the right way, justified the
right
way, etc.

That way, you can simply have links to the corresponding cells on the
doing-the-work sheet, and nothing but formatting on the other sheet.

To do the filling, try modifying this. In a fresh new workbook,
put the numeral 5 on sheet2, cell A1. Then in sheet1, cell A1,
put this.

=LEFT("0000000000",10-LEN(Sheet2!A1))&Sheet2!A1

For getting exactly enough rows, you can either do it manually,
or write yourself a simple little macro that deletes all but the
first row, finds the bottom row on your do-the-work sheet, then
fills down the formatted sheet that far. Use the macro recorder
to find the codes to do that.
Socks

 

[tankgirl215]

Thank you,

I don' t know anything about writing macros though, so I'm afraid I get
stuck at that point.

Any suggestions?

 

[Dave Peterson]

I would put a giant formula in an extra column on the same sheet (slightly
different from Puppet_Socks' technique).

=TEXT(A1,REPT("0",10)) & "," & RIGHT(REPT(" ",25)&UPPER(B1),25) & ","
& REPT(" ",120) & TEXT(C1,"yyyymmdd") & "," & LEFT(UPPER(D1)&REPT(" ",20),20)

And as much more as you need. Then format this column with Courier New (a nice
fixed width font that will make checking a bit easier) and drag down the column.

Then when I need that ASCII file, I'd just copy that column, paste into NotePad
and save from there.

=text(a1,rept("0",10))
will turn 123 into 0000000123

then a comma

RIGHT(REPT(" ",25)&UPPER(B1),25)
will turn
asdf
into
+++++++++++++++++++++ASDF
(where + represents a space--just for reading)

Then another comma

REPT(" ",120)
will be 120 spaces

Then another comma

TEXT(C1,"yyyymmdd")
Will turn
June 12, 2006
into
20060612
(if you needed to format a date)

and then another comma

and then
LEFT(UPPER(D1)&REPT(" ",20),20)
will turn
asdf
into
ASDF++++++++++++++++
(again + means space for reading)

After you get the formula working perfectly, copy it down your column.

If it's too much clutter, you can hide that column and only show it when you
need to use it.

 

[tankgirl215]

Thanks!

One question - the file I'm making is going to have 10,000 rows and 99
columns...each of which has its own unique character number
requirements like those I described in my initial question. Is there
anyway I can set this up without having to have extra columns all over?
99 is a lot to begin with, lol

 

[Dave Peterson]

I used one extra column. My formula concatenated all the text into one cell
(per row).

But if each row has a different layout, it's gonna take a lot of time to get
those formulas correct.

 

[tankgirl215]

I'm sorry - I'm not sure I explained that properly. It's each of the
99 columns that has a different format. The 10,000 rows are just the
product information defined by each column.

 

[Dave Peterson]

Each formula can only be 1024 characters long (when measured in R1C1 reference
style).

You may need a few intermediate columns to build the strings the way you like
them--and then an extra column that concatenates those intermediate cells.

I don't know of any other way to do it except to do it. And I find that doing
the formatting in code can be more difficult to get perfect than working with
formulas in the worksheet--your mileage may vary, though.

I would think that the time it takes to write down the specifications for each
of the 99 fields would take as long as building those formulas????