Excel to Txt file

[Mr_Philip]

Hi,

I need to convert an excel sprteadsheet to a txt file to be used in an
EDI upload to another system. For the data to upload from the txt file
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 a
excel document to a txt file?

 

[Pete_UK]

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

 

[Mr_Philip]

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 bac
to

27.31
321.52.

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