Although the reverse is easy, export of an excel worksheet as a fixed width text file (.txt) file is sometimes is not. Now, Excel does provide you with the option to save a worksheet as a text (.txt) or a Comma Separated (.csv) file, but if you had to ensure that each of the fields have their own specified lengths, this job is far from easy.
I recently came across a situation where we had to exchange data with a client whose system had a set specification. Each of the fields had a specific width (# of chars) and a simple comma separated or plain text file would not board in their systems properly. So yours truly came up with this simple utility that can helps you export an excel worksheet as a fixed width text file.
All you need to do to convert the data in an excel spreadsheet into a fixed width text file is to just select the data range that you want to export and click the ‘Export to Fixed Length Format’ button. Just ensure that the length of the fields are specified in the first row of the worksheet. Essentially what happens in the back end is that the VBA code looks at each row in the selection and for each of the fields in that row, reads the width to be assigned from the column header. Just a disclaimer – This code is neither ‘fool-proof’ nor has been tested for performance. But I am sure this would work like a charm for most of your day to day needs.
In its current format, this tool uses a space (” “) as the filler incase the data in a cell is shorter than the width specified for that column. If you want to change that, go to the VBA code and edit the “Filler_Char_To_Replace_Blanks” variable and set it to the one that you’d like to use. Let me know how this works out for you.