Fixed Width Text to Excel: Set the Column Breaks

To bring fixed width text to Excel, open the file through the Text Import Wizard, choose Fixed width, and click in the preview to place a break line wherever one field ends and the next begins. Set code and account columns to Text so zeros survive. Fix lines whose fields drift first, or they land in the wrong columns.

How do you spot a fixed width text file?

A fixed width file lines its fields up by character position instead of separating them with commas or tabs. Every value in a column starts at the same character count from the left, and shorter values are padded with spaces. Open it in a text editor with a monospaced font and the columns form straight vertical edges.

Fixed width layouts come from older systems: bank core systems, payroll exports, mainframe reports and many "download as text" options in business software. They also appear when a PDF is extracted to plain text with its layout kept, because the extractor pads lines with spaces to hold columns in place.

Three quick tests tell the formats apart:

• Search for the separator. If every line has the same number of commas or tabs, the file is delimited, not fixed width. Import it as delimited instead.
• Check the right edge of the numbers. In fixed width files, amounts usually line up on the right, so the decimal points form a column.
• Look for runs of spaces. Two or more spaces between values, varying from line to line, is the mark of padding.

One trap catches people here. A file can look fixed width in a proportional font and still be tab-separated, because tabs render as wide gaps. Switch the editor to show invisible characters. In most editors, tabs then show as arrows and spaces as dots.

The example file

This example report, made up for illustration, is used through the rest of the post. The ruler line below it counts character positions:

ACCOUNT   DATE      DESCRIPTION                  AMOUNT
0004417   20260302  COFFEE CART #12               -4.75
0004417   20260303  PAYROLL DEPOSIT             1850.00
0004417   20260305  CITY WATER BILL              -61.40
0009120   20260305  RENT TRANSFER               -910.00
1234567890123456789012345678901234567890123456789012345

The account sits in positions 1 to 10, the date in 11 to 20, the description in 21 to 45, and the amount in 46 to 55, right-aligned.

Turning on the legacy Text Import Wizard for fixed width text

The Text Import Wizard, the tool with the Fixed width option, is a legacy feature that current Excel hides by default. Turn it on under File, Options, Data, by ticking From Text (Legacy) under Show legacy data import wizards. It then appears under Data, Get Data, Legacy Wizards.

Those steps come from Microsoft's Text Import Wizard help page, which also says the wizard opens when you double-click the text file in the Import Text File dialog. Once it is enabled, the import runs in three steps:

1. Step 1, data type. Choose Fixed width. Set Start import at row to skip title lines above the header. Leave File origin at its default unless the text came from a system with a different character set.
2. Step 2, field widths. Place the break lines, covered in the next section.
3. Step 3, column formats. Choose a format for each column, and use Advanced to set decimal and thousands separators.

The modern From Text/CSV command is built for delimited files and guesses columns from a separator. It is the right tool for CSV, and the wrong one for this job unless you split the columns afterwards in Power Query, which the last section covers.

Setting column breaks on fixed width text in Excel

In step 2 of the wizard, a vertical line in the preview marks each column break. Click the preview at a position to add a break, drag a line to move it, and double-click a line to remove it. Excel proposes breaks where it sees blank columns, and you correct them against the field positions.

For the example file, the breaks belong after positions 10, 20 and 45. Excel will usually guess the first two correctly, because the gap of spaces between account and date runs through every line. The break between description and amount is harder. The longest description ends early, so Excel may put the break somewhere inside the blank run, which is harmless, or miss a field that only one line fills.

Place breaks by the layout, not by the data you can see. If the system's documentation says the description is 25 characters wide, put the break at 45 even when no description in this file is that long. Next month's file may have one.

A break table for the example

Write the layout down before you import. It turns guesswork into a checklist and documents the file for whoever does this next.

With those settings the four data lines arrive as four rows of four cells. The first data row reads 0004417, 2 March 2026, COFFEE CART #12 and -4.75.

Spaces need a moment's thought. Fixed width fields are padded, so a description cell may carry trailing spaces into Excel. Run TRIM on text columns afterwards, or the padding will spoil lookups and matching.

Keeping codes and account numbers as text in a fixed width import

Set any column of codes, account numbers or references to Text in step 3. With the General format, Excel reads 0004417 as the number 4417 and drops the zeros, and long digit strings can turn into scientific notation. A Text column keeps the characters exactly as the file holds them.

Select the column in the step 3 preview and pick Text under Column data format. Do this for every identifier, including ones that look harmless today. A branch code like 0120 and a check number like 000731 both lose meaning without their zeros.

Dates deserve the same care. A column of 20260302 values imported as General becomes the number 20,260,302. Choose Date and the order the file uses, here YMD, and Excel builds a real date. If the file uses day-first dates, pick DMY instead, or 03/04 will become the wrong month.

Amounts have their own setting. Click Advanced in step 3 to say which characters the file uses as decimal and thousands separators, and tick the trailing minus option if negatives appear as 4.75-. Microsoft's wizard page notes that the separators otherwise follow your regional settings, which is how a European file with decimal commas ends up as text.

If you want to skip a column entirely, such as a filler field, choose Do not import column (skip).

What if fixed width fields drift from line to line?

Drift means a field starts at a different position on some lines, so a fixed break cuts through it. The usual causes are a tab character hiding in the padding, a multi-byte accented character, or a report that was edited by hand. Find the lines that drift and fix them in the text before importing.

Spot drift in the wizard's preview. Scroll down step 2 and look for values that cross a break line, like a description whose last letters spill into the amount column. In the finished sheet, drift shows up as amounts stored as text, dates that failed to convert, or descriptions with digits stuck to the end.

Fixes, by cause:

• Tabs in the padding. Replace each tab with the right number of spaces, or with the separator of a delimited format if the whole file is mixed.
• Accented or non-Latin characters. Some systems count bytes rather than characters, so one accented letter shifts the rest of the line by one. Open the file with the right encoding in step 1, under File origin, and recheck the preview.
• Hand-edited lines. Compare the broken line with its neighbours and restore the padding.
• Two record types in one file. Some exports mix header records and detail records with different layouts. Split them into two files, often by the first character of the line, and import each with its own breaks.

When drift is widespread and irregular, the file is not really fixed width. Treat it as messy text, and consider whether the system can export a delimited format instead. A comparison of the structured alternatives is in JSON vs CSV for statement data.

Report headers, totals and page breaks inside fixed width text

Report-style fixed width files repeat a title block and column names at the top of every page, and add subtotal and grand total lines. Those lines do not fit the field layout, so they import as junk rows. Remove them from the text first, or filter them out in Excel straight after import.

Lines to look for:

• Page headers. Bank name, report date and page numbers, repeated on every page.
• Repeated column titles. The ACCOUNT, DATE, DESCRIPTION line printed again after each page break.
• Dashed rule lines. Rows of hyphens or equals signs used as underlines.
• Totals. Lines with a label like TOTAL and a number in the amount column but no date.
• Form feeds. Invisible page-break characters that some editors show as a small symbol.

A filter on the date column catches most of them. After import, filter for rows where the date cell is not a real date, check what they are, and delete them.

Some fixed width files have no column title line at all, and the layout lives in a separate document. Then you add the names yourself after import. Working with a CSV that has no header row covers naming columns before the data goes further.

Repeating a fixed width import every month with Power Query

When the same layout arrives every month, set the split up once in Power Query. Load the file so each line is one column, then use Split Column, By Positions with the start positions of each field. Refresh next month and the same breaks apply to the new file.

Microsoft's Power Query guide to splitting columns by positions says positions are zero-based and comma-separated, with zero as the start of the text. For the example file the positions are 0,10,20,45. The new columns come out named Column1.1, Column1.2 and so on, which you rename and give data types: Text for the account, Date for the date, Decimal Number for the amount.

Add the cleanup as steps in the same query: remove the header line, filter out rows whose date fails to convert, and trim the description. Each refresh repeats all of it.

Not every fixed width text began life as text. A lot of it comes from extracting a PDF report with layout mode, and in that case converting the PDF straight to rows avoids the text stage. For a worked case of that kind of source, see turning a medical bill PDF into an Excel table. For bank statements specifically, FastBankConvert is built to put each transaction in its own row and hand back Excel or CSV; it is getting ready to open, and the get started page is where to hear when trials begin.