The first thing most people do when they get a .txt file from a supplier, log export, or legacy system is open it in Notepad, then copy-paste into Excel. That’s almost always the wrong move — especially if the file uses tabs, pipes, or inconsistent spacing. You’ll end up with merged columns, misaligned dates, and numbers stored as text in column A. Trust me, I learned this the hard way after fixing a payroll report where '12/05/2024' became '12/5/2024' and then '5-Dec' because Excel auto-converted it before we caught the format drift.
The Problem
You receive sales_export_202403.txt from your ERP. It looks fine in Notepad — but paste it into Excel and everything collapses. Commas are missing, fields run together, and the 'Amount' column shows '45200.00' as text (left-aligned), while 'Order Date' turns into random serial numbers like 45372. Worse: some rows have extra spaces, others use semicolons, and one line has an unescaped quote that breaks the whole paste.
| Row | Raw Text Line | What Paste Into A1 Gives You |
|---|---|---|
| 1 | ID|Name|Amount|Date|Status | ID|Name|Amount|Date|Status (all in A1) |
| 2 | 1001|Sarah Chen|$45,200|2024-03-15|Shipped | 1001|Sarah Chen|$45,200|2024-03-15|Shipped (all in A2) |
| 3 | 1002|Acme Corp|$12,850|2024-03-16|Pending | 1002|Acme Corp|$12,850|2024-03-16|Pending (all in A3) |
| 4 | 1003|"Beta Systems, LLC"|$8,900|2024-03-17|Delivered | 1003|"Beta Systems (A4), LLC"|$8,900|2024-03-17|Delivered (split across A4–B4) |
| 5 | 1004|Delta Tech|$32,100|2024-03-18|Cancelled | 1004|Delta Tech|$32,100|2024-03-18|Cancelled (all in A5) |
The Solution
We don’t paste. We import. Excel’s built-in Text Import Wizard handles delimiters, quoting, and data types — and it’s faster than selecting, copying, and praying. Here’s how:
- Go to Data → Get Data → From Text/CSV (Alt+A, T, T). Yes — not the old 'From Text' button. The modern ribbon path works reliably with UTF-8, BOMs, and embedded line breaks.
- Select your
.txtfile. Excel previews it. If you see columns stacked vertically instead of side-by-side, click 'Transform Data' — don’t close the preview window yet. - In Power Query Editor, check 'Delimiter' under 'Split Column'. Choose 'Custom' and type
|(pipe) — or whatever separator your file actually uses. If unsure, try Tab first — many log exports use tabs, not commas. - Right-click each column header and choose 'Change Type'. Set 'Amount' to 'Decimal Number', 'Date' to 'Date', and 'ID' to 'Whole Number'. This fixes the $45,200-as-text problem before it hits your worksheet.
- Click 'Close & Load'. Your clean table lands in a new worksheet starting at A1 — properly split, typed, and formatted.
Here’s what you get instead:
| ID | Name | Amount | Date | Status |
|---|---|---|---|---|
| 1001 | Sarah Chen | 45200.00 | 2024-03-15 | Shipped |
| 1002 | Acme Corp | 12850.00 | 2024-03-16 | Pending |
| 1003 | Beta Systems, LLC | 8900.00 | 2024-03-17 | Delivered |
| 1004 | Delta Tech | 32100.00 | 2024-03-18 | Cancelled |
| 1005 | Zeta Labs | 6750.50 | 2024-03-19 | Shipped |
Going Further
You can automate this. Save the Power Query as a connection, then refresh with one click when next month’s sales_export_202404.txt arrives. No re-importing. Just right-click the table → 'Refresh'.
If your TXT file changes structure monthly (e.g., new columns added), edit the query: In Power Query Editor, go to 'Home' → 'Advanced Editor', and look for lines like Table.SplitColumn(..., "|", ...). Change the delimiter or add Table.TransformColumnTypes for new fields.
For files with no consistent delimiter — just space-separated values with irregular gaps — use 'Fixed Width' in the initial preview. Click the ruler above the preview pane and drag break lines between columns. Then promote headers and set types.
Surprising tip: If Excel misreads your date as 'General', don’t reformat the cell. That only changes display — not the underlying value. Instead, in Power Query, right-click the column → 'Change Type' → 'Date', then reload. That ensures formulas like =TODAY()-B2 return correct day counts.
When NOT to Use This
Avoid the Text Import Wizard for files over 1 million rows. Excel will hang or crash. Use Power BI Desktop or Python (pandas + read_csv) instead — then export the cleaned result to Excel.
Don’t use it for TXT files that are actually CSVs with comma-delimited fields but double-quoted text containing commas (e.g., "Smith, John",25000,2024-03-15). Excel’s CSV importer handles quotes better than the generic Text importer. Use 'From Text/CSV' but rename the file to .csv first — or use Data → From Legacy Wizards → From Text (Legacy).
Also skip this method if the TXT file contains binary content, null bytes, or mixed encodings (e.g., some lines UTF-8, others Windows-1252). Open it in VS Code first, check encoding in the bottom-right corner, and resave as UTF-8 before importing.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open 'From Text/CSV' | Alt → A → T → T | Faster than hunting through ribbons |
| Promote Headers in Power Query | Ctrl + Shift + H | Use after splitting columns |
| Change Column Type (Power Query) | Ctrl + Shift + C | Then press D for Date, N for Number, T for Text |
| Refresh All Queries | Alt → F5 | Saves minutes on recurring imports |
