Exporting to a txt file in a certain format.

J

johnfli

I have an excel sheet that I need to export into a certain format.
I will need to have it so each column will only export the first (x) amount
of characters in each cell, then add a SPACE, then add the first (x) amount
of characters from teh next cell.

How do I do that?
 
D

Dave O

Hi, John-
Suppose your existing data is in a tab called Sheet1; cell A1 contains
the text string ABCDE; cell B1 contains a number 12345.

Insert a new tab in the workbook- for purposes of this example I used
the default name Sheet2. In cell Sheet2!A1 enter this formula:
=MID(Sheet1!A1,1,3)&" "&MID(Sheet1!B1,1,3)
The result in Sheet2!A1 is "ABC 123".

The MID function returns a portion of a string function that you
indicate: in this example, it starts in Sheet1!A1 in column 1 and
returns the first 3 columns. The ampersand in the middle is a
concatenator that joins the results of different functions.
Can you modify this example to fit your application?
 
E

Earl Kiosterud

John,

Here's a solution similar to that of Dave O.

In a separate mirror sheet, in A1:

=LEFT(Sheet1!A1, x)

Copy this sufficiently down and across using the Fill Handle. Now write
this sheet using the Text Write Program (a free Excel workbook) at
www.smokeylake.com/excel. Set the field delimiter to a space.
 
J

johnfli

Yeah, I might be able to use that. Thanks.
After testing it, I'll convert it into a macro.

Thanks again!
 
Top