Exporting with spaces

[Justin Fisk]

I need to do something rather complex... I have a bunch of Multi-Row,
5-Column sheets that need to be formatted in the following format (as a text
file)

Text(Col1) Spaces Text(Col2) Spaces Text(Col3) Spaces Text(Col4) Spaces
Text(Col5)
The second set of text (col2) needs to start at 16 char then the field goes
for the next 14 char...
Col3 Starts at 30 char and goes for the next 14 chars... and etc...
Ideally this leads to a text file that "resembles" the excel file given that
all the columns could have up to 14 char.

Is there a way to get excel to export my data in the above format?

 

[Dave O]

Paste this formula into an open cell on each row of your spreadsheet:
it will join the cells in each row together, and separate entries by
the required number of spaces. You can then paste the results into
Notepad and save as text, or save the spreadsheet as text.

=A1&REPT(" ",15-LEN(A1))&B1&REPT(" ",15-LEN(B1))&C1&REPT("
",15-LEN(C1))&D1&REPT(" ",15-LEN(D1))&E1&REPT(" ",15-LEN(E1))

.... where REPT() is a repeat function, and LEN() shows the length the
entry.

Note that this will fail if any of the entries exceed 15 characters in
length. To prevent that, use the MID() function:

=MID(A1,1,15)&REPT(" ",15-LEN(MID(A1,1,15)))&MID(B1,1,15)&REPT("
",15-LEN(MID(B1,1,15)))&MID(C1,1,15)&REPT("
",15-LEN(MID(C1,1,15)))&MID(D1,1,15)&REPT("
",15-LEN(MID(D1,1,15)))&MID(E1,1,15)&REPT(" ",15-LEN(MID(E1,1,15)))