How to Open a TXT File in Excel (And Actually Get the Data Right)

Opening a plain text file in Excel sounds simple — drag, drop, done. But anyone who's tried it knows the result can be a mess: all your data crammed into a single column, or numbers that look fine until you realize Excel quietly reformatted them. Getting it right depends on how you open the file, not just that you open it.

What Is a TXT File, Really?

A .txt file is a plain text file with no built-in formatting — no fonts, no cell structure, no data types. When it contains tabular data, that structure is implied by delimiters: characters that separate one value from the next.

The most common delimiters in TXT files are:

Excel needs to know which delimiter your file uses. Get that wrong, and the import falls apart.

Method 1: Open Directly from Excel

The most reliable way to open a TXT file is to launch the import process from inside Excel, not from File Explorer.

1. Open Excel and go to File → Open
2. Browse to your TXT file and select it
3. Excel detects it's a text file and automatically launches the Text Import Wizard

The wizard walks you through three steps:

• Step 1: Choose Delimited or Fixed Width. Most modern exports are delimited. Fixed width is used when every column occupies a set number of characters regardless of value length.
• Step 2: Select your delimiter. If you're unsure, check the preview pane — the correct delimiter will produce clean column breaks.
• Step 3: Set column data formats. This step matters more than most people realize (more on that below).

Method 2: Use the "Get Data" Import Tool (Excel 365 / Excel 2016+)

Newer versions of Excel replaced the classic wizard with Power Query, accessible via:

Data → Get Data → From File → From Text/CSV

Despite the label saying "CSV," this works for .txt files too. Power Query automatically detects the delimiter in most cases and shows a clean preview before you commit. It also gives you more control over data transformations before the data lands in your sheet.

The key difference: Power Query loads data as a query, meaning you can refresh it later if the source file updates. The classic wizard creates a one-time static import.

Method 3: Drag and Drop (What to Expect)

Dragging a TXT file directly onto an open Excel window may trigger the Text Import Wizard, or it may open the file as raw text in a single column — depending on your Excel version and system settings. This method is unpredictable and generally not recommended when data structure matters.

The Part Most People Get Wrong: Data Formatting 📋

Even when the columns split correctly, Excel can misread the type of data inside them.

Common problems:

• Long numbers (product codes, phone numbers, ZIP codes) get converted to scientific notation or lose leading zeros — 007 becomes 7
• Dates get reformatted based on your regional settings, sometimes incorrectly
• Text that looks like numbers gets treated as numbers, which can change the value

In the Text Import Wizard (Step 3) or in Power Query, you can manually set each column's data type:

• General — Excel guesses (risky for edge cases)
• Text — Treats the column as plain text, preserving leading zeros and long strings
• Date — Lets you specify the date format in the source file (MDY, DMY, YMD)
• Do not import column — Skips columns you don't need

Setting column types explicitly before importing is the single most effective way to prevent data corruption.

When Your TXT File Has No Clear Structure

Some TXT files aren't clean exports — they're log files, notes, or mixed-format data. In those cases, Excel may not be the right tool at all. You might get better results opening the file in a text editor first to understand its structure, then deciding whether Excel's import tools can handle it or whether pre-processing is needed.

Fixed-Width Files Deserve a Mention

If your TXT file uses fixed-width formatting (common in legacy banking, government, and mainframe exports), there are no delimiter characters. Instead, each field occupies a precise number of characters. The Text Import Wizard handles this — you draw column break lines manually in the preview. It's fiddlier than delimited imports, but it works once you know the column widths (usually documented in a companion spec sheet or data dictionary).

What Actually Determines the Right Approach 🔍

The cleanest method for any given file depends on a few variables that differ from one user to the next:

• Your Excel version — The classic Text Import Wizard behaves differently from Power Query, and not all versions have both
• The delimiter used in the file — A tab-delimited file and a pipe-delimited file need different settings
• Whether data types matter — Casual review versus a production workflow have very different tolerances for formatting errors
• Whether the file will update — If the source TXT file refreshes regularly, a Power Query connection makes that easier to manage than re-importing manually each time
• File size — Very large TXT files can behave differently depending on Excel's row limits and available system memory

The method that works perfectly for a small, tab-delimited export from one system might produce completely wrong results with a fixed-width file from another. The structure of your specific file — and what you need to do with the data once it's in Excel — is what ultimately determines which approach fits.