Hi,

I need to convert an excel sprteadsheet to a txt file to be used in and
EDI upload to another system. For the data to upload from the txt file I
need the spacing of the data columns inthe txt file to be exact.

Question: How do I specify the spacing of the text when converting an
excel document to a txt file??


--
Mr_Philip
------------------------------------------------------------------------
Mr_Philip's Profile: http://www.excelforum.com/member.php...o&userid=31415
View this thread: http://www.excelforum.com/showthread...hreadid=524748

If you have a numeric field you can convert this to text of a certain
width by means of a formula like:

=TEXT(A1,"00000000")

This would make it 8 characters wide. If you want to make a text field
a certain width, then you can use a formula like:

=IF(LEN(B1)=20,LEFT(B1,20),B1&REPT(" ",20-LEN(B1)))

This will make it 20 characters wide - if the text is longer than this
to begin with it will be truncated, otherwise it will have sufficeient
spaces added on to the end to make it up to 20 characters.

You can adapt this approach to your other fields. I would suggest that
you do this in a second sheet, where all the formulae refer to Sheet1.
Then you can fix the values and it is Sheet2 that you will export as
the fixed-width text file.

Hope this helps.

Pete

Thanks!!

One more problem. Some of the text is formatted to read for example:

0000000000027.31
0000000000321.52

when I write the above formula it loses the formatting and goes back
to

27.31
321.52.

Is there any way around this? it will not accept a reformat.


--
Mr_Philip
------------------------------------------------------------------------
Mr_Philip's Profile: http://www.excelforum.com/member.php...o&userid=31415
View this thread: http://www.excelforum.com/showthread...hreadid=524748