Text files separate data using delimiters — characters like commas, tabs, or pipes that mark field boundaries. But Excel often guesses wrong, merges columns, or truncates numbers with leading zeros — and won’t tell you.
Quick Answer
Excel separates text file data using delimiters (commas, tabs, semicolons, etc.) during import — but it auto-detects them inconsistently, especially when data contains embedded commas or mixed date formats. Always use Get Data > From Text/CSV, not double-clicking, or you’ll lose control before the first row loads.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Double-click .csv/.txt | Open file directly in Excel | Quick preview of clean, uniform data | Ignores encoding (breaks Chinese/Japanese), skips delimiter detection, forces regional settings (e.g., comma = decimal in Germany) |
| Data > From Text/CSV | Select file → Preview → Choose delimiter → Load | All production imports — handles UTF-8, custom delimiters, headers | Requires Power Query engine (Excel 2016+); older versions use Legacy Import Wizard |
| Text to Columns (Delimited) | Select column → Data tab → Text to Columns → Delimited → choose separator | Fixing already-imported messy columns (e.g., "Smith, John" in A1) | Only works on existing data in worksheet — no encoding control, no preview, overwrites original column |
| Power Query Advanced Editor | Edit query → open Advanced Editor → modify Delimiter=Comma or add Encoding=65001 |
Batch imports with nonstandard separators (e.g., |~|), fixed-width + delimiter hybrids | Requires M language familiarity; no GUI fallback if syntax breaks |
| Legacy Text Import Wizard (Alt+A+E) | Alt+A+E → Step 1: Select Delimited/Fixed width → Step 2: Choose separator → Step 3: Set column data types | Excel 2013 or older; strict control over date/number formatting per column | No UTF-8 support; crashes on BOM-heavy files; missing preview pane |
Method 1 Deep Dive
Use Data > From Text/CSV — this is your only reliable method for modern Excel. It’s not optional. Don’t skip the preview screen.
Try it now with this sample CSV (save as sales_q1.csv):
| ID | Name | Region | Amount | Date |
|---|---|---|---|---|
| 1042 | Sarah Chen | APAC | $45,200 | 2024-03-15 |
| 1087 | Miguel Rios | EMEA | $32,850 | 2024-03-18 |
| 1102 | Anya Petrova | EMEA | $51,000 | 2024-03-22 |
| 1155 | James Okafor | AMER | $29,400 | 2024-03-25 |
| 1209 | Priya Mehta | APAC | $38,600 | 2024-03-29 |
Click Data > From Text/CSV. Navigate to the file. Click it. In the preview window:
- Confirm File Origin is set to
65001: Unicode (UTF-8)— critical for names like “Petrova” or “Mehta” - Under Delimiter, check Comma. If it’s grayed out, click the pencil icon to re-parse.
- Click the column header “Amount” → right-click → Change Type → Currency.
- Click “Load To…” → select New Worksheet → OK.
The result lands cleanly in Sheet2, starting at cell A1. No text-wrapping. No $ signs stripped. No dates flipped to 3/15/2024 when your locale expects DD/MM/YYYY. That’s because Power Query respects ISO date format by default — unlike double-clicking.
Method 2 Deep Dive
When your text file uses a custom delimiter — say, pipe + tilde (|~|) — Excel won’t auto-detect it. You need Power Query Advanced Editor.
Start with Data > From Text/CSV, select the file, and click Transform Data instead of Load. In Power Query Editor, go to Home > Advanced Editor.
You’ll see code like this:
let
Source = Csv.Contents(File.Contents("C:\data\log_export.txt"), [Delimiter="|", Columns=5, Encoding=1252]),
...
Change it to:
let
Source = Csv.Contents(File.Contents("C:\data\log_export.txt"), [Delimiter="|~|", Columns=5, Encoding=65001]),
...
Press Done. Now the preview splits correctly. Bonus tip: If Excel misreads your first row as headers when it’s actually data, click the gear icon next to “Promoted Headers” in the Applied Steps pane and uncheck Use First Row as Headers. This saves hours debugging later.
Here’s what happens if you skip this: Your log file has 7 fields, but Excel merges columns 3–5 into one cell because it sees | inside a quoted string like "Error|Timeout|Network". The |~| delimiter avoids that — but only if you tell Power Query explicitly.
Cheat Sheet
| Task | Shortcut / Path | Critical Detail |
|---|---|---|
| Import CSV safely | Data > From Text/CSV | Never double-click — bypasses encoding & delimiter control |
| Force UTF-8 encoding | In preview → File Origin → 65001: Unicode (UTF-8) |
Fixes garbled Chinese, Arabic, or accented names instantly |
| Open Legacy Wizard | Alt+A+E | Only for Excel 2013 or compatibility mode — no UTF-8 |
| Split one column post-import | Data tab > Text to Columns > Delimited | Select entire column first — e.g., click A:A before launching |
| Custom delimiter in Power Query | Advanced Editor → change Delimiter="|" to Delimiter="|~|" |
Add Encoding=65001 on same line if file has special characters |
