Excel has an in-built ability to import fixed-width text files. If you try to open a text file, a wizard will appear asking whether it's fixed-width or delimited.
Delimited files are easily processed. With fixed-width files, Excel has a go at figuring out where to insert the column breaks, but more often than not, you'll have to add some, delete others and move some more to get the desired result.
- To insert a break, just click where you want it
- To move a break, just drag it to where you want it
- To delete a break, double-click it.
The frustrating part about it all is that there's no way of saving the layout that you've defined. If you're importing files of the same layout time and time again, this can be a pain.
But there is a solution.
Create a new workbook containing two sheets. One will house the raw data; the other will split it into columns.
Copy your raw data into the top of the first sheet using the traditional copy/paste method. This will all be stacked up in column A.
In the second sheet, we're going to create a frame for separating the data out into its constituent parts.
In row 3, put your column headers. In row 1 put that column's start position in the file. And in row 2, put the length of the field.
Now we'll use the MID function, together with dollars to hold our references, to put data in row 4.
You only need to create the formula in cell B4. Once you've done that, copy it across to the right-most column. Then copy that row down to cover as many records you copied into the original sheet.
I use a spreadsheet just like this regularly, calling it Template. And I pin it to my Excel Recent Documents list so that it's always only two clicks away.