How to Convert Fixed-Width Text Into a Spreadsheet
Legacy accounting systems, AS/400 mainframes, and banking software often export plain text reports where columns are visually aligned using the spacebar without delimiter characters.
Fixed-width text formats align columns using space characters at predetermined character positions rather than delimiters like commas or tabs. To convert fixed-width text into a spreadsheet: 1) Paste the text into TextToTable, which detects multiple-space column boundaries automatically while preserving single spaces inside words (e.g. "New York"). Click "Copy to Excel" to paste directly into columns. 2) In Excel, use Data → From Text/CSV or the legacy Text to Columns wizard and select "Fixed width" to manually place column break lines on a character ruler.
In This Guide
1. Why Does This Problem Happen? (Root Causes)
No Delimiter Characters: Fixed-width files do not contain tabs or commas; columns are defined by character offsets (e.g. chars 1–10 is ID, 11–35 is Name).
Multi-Word Cell Collisions: Splitting on single spaces destroys multi-word cities, full names, and product descriptions ("New York" splits into two columns).
Uneven Padding: Different records have varying amounts of trailing spaces before the next column starts.
2. How to Fix It (Step-by-Step Methods)
Method 1: Smart Multi-Space Auto-Detection in TextToTable
Paste your fixed-width text into TextToTable. The tool's algorithm calculates recurring multi-space boundaries while recognizing that single spaces between letters belong to the same text cell. Review the generated table and copy or download XLSX.
Method 2: Excel Fixed Width Wizard
In Excel, select your column of text. Go to Data → Text to Columns → Select "Fixed width" → Click Next. Excel displays a vertical ruler. Click along the ruler to create column break lines, double-click a line to remove it, or drag a line to move it. Click Finish.
3. Verified Test Fixture & Output
Reproducible test input and the verified multi-column result.
EmpID Employee Name Department Salary 10045 Alexander Wright Financial Services $85,000 10046 Beatrice Montgomery Quality Assurance $92,500 10047 Carlos Santana Product Logistics $78,000
| EmpID | Employee Name | Department | Salary |
|---|---|---|---|
| 10045 | Alexander Wright | Financial Services | $85,000 |
| 10046 | Beatrice Montgomery | Quality Assurance | $92,500 |
| 10047 | Carlos Santana | Product Logistics | $78,000 |
4. Common Pitfalls to Avoid
5. Frequently Asked Questions
Why does my fixed-width text look wavy and misaligned in Notepad?
If Notepad is set to a proportional font like Arial or Calibri, characters like "i" take less horizontal width than "w", making perfectly aligned columns appear jagged. Enable a monospace font like Consolas to view the true alignment.
Can fixed-width files have empty columns?
Yes, if optional fields are omitted, the space offset remains reserved. TextToTable includes a "Remove Empty Columns" toggle to clean up empty columns automatically.
Related Guides & Tutorials
How to Split Text Into Columns in Excel (Step-by-Step Guide)
Complete tutorial on splitting plain text into multiple columns in Excel. Covers the Text to Columns wizard, TEXTSPLIT formulas, Flash Fill, and common mistakes.
Practical WorkflowHow to Clean Copied PDF Table Text into Excel
Step-by-step workflow to convert messy text copied from PDF tables into clean Excel spreadsheets without manual retyping.
Authoritative Technical References & Standards
- • IETF RFC 4180 Specification: Common Format and MIME Type for Comma-Separated Values (CSV) Files.
- • Microsoft Excel Documentation: Import or export text (.txt or .csv) files in Microsoft Excel.
- • Unicode Consortium Standards: UTF-8, UTF-16, and Byte Order Mark (BOM) Specifications.