How do I create a fixed width text file?

excel text-files fixed-width export-to-text
Craigoh1 picture Craigoh1 · Jan 30, 2014 · Viewed 42.3k times · Source

I have a fixed width text file that I needed to edit about 200 rows of. Importing into excel is easy but when I have completed my edits and try to save the file as a space-delimited or text file all the spacing goes out of whack, i.e the first field in excel is padded out to 6 characters but when I save the file as space-delimited or text it then turns that field into 8 characters.

Please note that I'm using a LEFT(text&REPT(" ", 30)30) formula to get the required padding which works very nicely. However I can't seem to save the file with the correct number of spaces. I have also just tried copying and pasting into a notepad file but this seems to just create more unwanted spaces etc.

How do I create a fixed width file when I have all the data I need and the field length requirements?? Has anyone had this trouble before? Thanks in advance.

Answer

Kenneth Hampton picture Kenneth Hampton · Jan 30, 2014

I agree with Gary's Student. Just go to: Save As -> Formatted Text (Space Delimited) (.prn)*.This will bring almost same functionality as one you have in Excel.

For more information you may refer to: https://superuser.com/questions/100433/export-an-excel-spreadsheet-to-fixed-width-text-file

Related questions

Read lines from a text file but skip the first two lines

I've got this macro code in Microsoft Office Word 2003 which reads the lines of a text file. The lines each represent a string value that I need to use later in the code. However, the first two lines of the …

Read More
How to convert a tab separated file to CSV format?

I have a text file in this format : { attribute1 attribute2 attribute3.... attributeN value"A" value"B" value"C".... value"Z" /* next line of values*/ } Each word is separated by a tab. How do I convert to CSV format? I tried …

Read More
Reading data from text file and delimiting

I have an Excel 2010 spreadsheet, and I am reading in information from a .txt file (and another .xls file in future). This text file has 3 elements per row; firtname, surname and Job title, and each element is separated by a …

Read More