Fixed Width Text Files
Recently, I had to submit a fixed width text file to a state tax agency in lieu of sending them physical copies of W-2 forms. Fixed width files contain a number of columns and each column starts at a particular position on the line. If the data in that column is shorter than the width of the column, the column is padded with spaces so that the next column starts in the correct position.
I had the data in Excel, I just needed to get it to a text file. As it happens, I only have one employee for this state, so it would probably have been just as quick to type it manually. But I think we all know why I didn’t do that. The spec for the file is this:
| Field | Start | Length | |
| Full Name | 1 | 50 | |
| SSN | 51 | 9 | |
| Wages | 60 | 8 | |
| Withholding | 69 | 8 |

Here’s the monster formula in F1:
=A1&REPT(" ",A2-LEN(A1))&SUBSTITUTE(B1,"-","")&REPT(" ",B2-LEN(SUBSTITUTE(B1,"-","")))&C1&
REPT(" ",C2-LEN(C1))&D1&REPT(" ",D2-LEN(D1))
The basic structure is cell value & repeat a space to fill. The exception is B2 where I had to remove the dashes from the social security number. If I had more than one row to do, I probably would have put the field lengths above the data. Then I could fill down column F. Finally I would copy F1 to a blank Notepad file, and there’s my fixed-width text file.




