exportfixed widthmicrosoft-excel-2003
Excel has the functionality to import fixed-width text files where it presents a dialog that lets you choose where the begins and ends of fields are which it puts into columns.
Does it also have functionality where, given an existing spreadsheet, you can export to a fixed-width text file?
If so, how do I access this? I have tried using Save As and choosing Text File but it seems to only save as Tab-delimited which doesn't help me.
This is Excel 2003 if it matters.
This is certainly not simple to explain... but after it is setup it will work with minimal effort. Also this is a slight variation on what @pnuts has outlined
I suggest using a template spreadsheet that you can copy-paste to. the template i have created has 3 tabs that look as follows.
Data tab is where the data is pastedLength is going to do some math to determine the longest columnSpace insert is going to insert the appropriate number of spaces (edit) you can copy from here and get the correct resultthe length tab looks like
Row 1 has maximum characters of the column below is and contains the formula
=MAX(A2:A101)+1
+1 creates the delimiter.
Row 2 through to n (which i have extended to 100 for this example) contain a formula to evaluate the length of each string
=LEN('Data tab'!A1)
the space insert tab looks like
Each cell contains a cell to evaluate the length of itself in comparison with the max value (+ delimiter) and insert an appropriate number of spaces.
='Data tab'!A1&REPT(" ",length!A$1-length!A2)
Note the $ which locks Row 1 if you copy and paste the formula
(edit) you can copy from the space insert tab.
If you are using Windows 10 then you can use this solution:
I have also attached a screenshot:
Best Answer
I think the closest you can get from native Excel functionality is Save As | Formatted Text (Space Delimited) (*.prn). It will automatically determine the widths and insert spaces to pad to that width as necessary.
Beyond that you need to have a macro or other add-in that will let you do more.