Fixed File Merge

[Dylan&Hayden]

I was trying to handle this in Access and resigned to completing this task manually. Now that I have played around with Excel a bit I think it can be done in Excel.

I have a fixed file .txt format that I need to match for a customer.

29 digits
1st row = total dollars = total.txt
2nd row = Total item count = count.txt
Remaining rows are detail information = detail.txt

All three of these files are exported out of Access and I need to get them in a single file in the format above.

The detail.txt is perfectly formated and all I am trying to do would be copy the two summary rows as the headers with enough leading zero's to end at a 29 fixed format.

I know I can do a copy and paste command but how do I find out how many digits are used in the summary rows and offset this with enough leading zero's to equal 29? The values in the header rows can change and I need to account for this with different number of leading zeros each time the export is ran.

The cells are formatted as text. Is there a count command digit formula that will tell me when I equal my "Magic Number" and fill in with enough leading zeros?

Any ideas?

[Dylan&Hayden]

Not pretty, but I know it can be done with

Len(a1) = 2
Formula. 29 - Len Results = 27 (A2)
Rept("0",A2)
Concatenate values: A2 & A1 = A3

Copy A3 to other file to get the desired results.

Can someone help me simply the above process?

[shg]

Maybe =RIGHT(REPT("0", 29) & A1, 29)

... or =REPT("0", 29 - LEN(A1)) & A1