How to Convert a Text File to Excel (Every Method Explained)
Converting a text file to Excel is one of those tasks that sounds simple until you actually try it — and then you realize there are several ways to do it, and the right one depends entirely on how your data is structured. Here's what's actually happening under the hood, and how to choose the approach that fits your situation.
What's Really Going On When You Convert Text to Excel
A text file (.txt or .csv) stores data as plain characters. There are no cells, no columns, no formatting — just rows of text, usually with some kind of separator between values. That separator might be a comma, a tab, a pipe character (|), a semicolon, or sometimes just fixed-width spacing.
Excel's job during import is to read that separator pattern and split each row into the correct columns. When this works cleanly, your data lands perfectly in a spreadsheet. When it doesn't, you end up with everything crammed into column A, or data split in the wrong places.
Understanding your delimiter before you start saves a lot of cleanup time.
Method 1: Open the File Directly in Excel
If your text file is a CSV (comma-separated values), Excel will often open it correctly with a simple double-click or File > Open — no extra steps needed. Excel recognizes .csv as a spreadsheet-ready format and handles the comma delimiter automatically.
When this works well: Clean CSVs exported from apps, databases, or other spreadsheets.
When it breaks down: If your CSV contains commas inside data fields (like addresses or descriptions), Excel can misread the column boundaries. It also won't help you with tab-delimited or pipe-delimited .txt files.
Method 2: Use Excel's Text Import Wizard 📋
This is the most reliable method for .txt files and for CSVs with messy or non-standard formatting.
In Excel (Windows):
- Go to Data > Get External Data > From Text (older Excel), or Data > Get Data > From File > From Text/CSV (Excel 2016 and later)
- Select your file
- The import wizard or Power Query preview opens
What you'll configure:
- Delimited vs. Fixed Width — Delimited means values are separated by a character. Fixed Width means each column occupies a set number of characters (common in legacy system exports).
- Your delimiter — Comma, tab, semicolon, space, or a custom character
- Column data types — Whether a column should be treated as text, a number, a date, etc.
The preview window shows you exactly how the data will land in your spreadsheet before you commit. This step is worth taking seriously — getting the data type wrong on a column of numbers or dates means reformatting work later.
In Excel on Mac: The path is slightly different (Data > Get Data > From Text), but the wizard works the same way.
Method 3: Power Query (Modern Excel) 🔄
In Excel 2016 and later (including Microsoft 365), Power Query is the preferred tool for importing text files, especially if you'll be refreshing the data regularly or need to clean it during import.
Power Query gives you:
- A visual column editor before data loads
- The ability to remove rows, rename columns, and change data types in a guided interface
- A refresh connection — so if the source text file updates, you can re-import with one click
This method is particularly useful for repeating workflows, like pulling in a daily export from accounting software or a log file from a server.
Method 4: Copy and Paste (Small Files Only)
For very small, simple text files, you can open the file in Notepad or any text editor, select all the text, paste it into Excel, and then use Data > Text to Columns to split it.
This works fine for a few dozen rows. For anything larger, it becomes error-prone and slow.
Key Variables That Affect Your Results
| Variable | Why It Matters |
|---|---|
| Delimiter type | Wrong delimiter = all data in one column |
| Text encoding | UTF-8 vs. ANSI affects special characters and accents |
| Date formats | Excel may misread international date formats (DD/MM vs MM/DD) |
| Leading zeros | Phone numbers and zip codes lose leading zeros if imported as numbers |
| File size | Very large text files (millions of rows) may exceed Excel's row limit |
| Excel version | Power Query availability and UI path vary by version |
Leading zeros and long numeric strings deserve special attention. If you're importing product codes, ID numbers, or zip codes, tell Excel to treat those columns as Text during import — not as numbers. Once leading zeros are stripped, you can't recover them without going back to the source file.
When the Data Doesn't Land Right
The most common problems after import:
- Everything in one column: Your delimiter wasn't recognized. Use Text to Columns (Data tab) to re-split using the correct separator.
- Garbled characters: An encoding mismatch. Try re-importing and selecting UTF-8 encoding in the wizard.
- Dates showing as numbers: Excel stored them as serial numbers. Format the column as Date.
- Numbers stored as text: A small green triangle appears in the cell corner. Select the column and use the "Convert to Number" prompt.
Fixed-Width Files: A Special Case
Some text files — particularly exports from older ERP systems, government databases, or mainframe software — don't use a delimiter at all. Instead, each field always occupies the same number of characters. A name field might always be 20 characters wide, followed by a 10-character ID, and so on.
For these, the Fixed Width option in the import wizard lets you manually set column break points by clicking on a ruler in the preview. It takes a bit more setup but works reliably once you know where each field starts and ends.
The method that works best comes down to details specific to your file: how it was created, what system exported it, how often you need to do this, and what you plan to do with the data once it's in Excel. A one-time import of a clean CSV is a very different task from a recurring workflow pulling in messy fixed-width exports — and Excel gives you tools for both ends of that spectrum.