How to Convert a Text File to Excel (Multiple Methods Explained)

Converting a text file to Excel is one of those tasks that sounds simple but has more nuance than most people expect. The right method depends on how your data is structured, which version of Excel you're using, and what you plan to do with the data once it's imported. Here's a clear breakdown of how it works.

What "Converting" Actually Means

A text file (.txt) stores data as plain characters with no formatting. Excel stores data in a structured grid of rows and columns. The conversion process is really an import and parse operation — Excel reads the raw text and figures out (or is told) how to split it into individual cells.

The key concept here is the delimiter — the character that separates one piece of data from the next. Common delimiters include:

• Comma (,) — standard in CSV files
• Tab () — common in exported reports and system data
• Pipe (|) — used in some database exports
• Space — used in simpler or fixed-width formats

If your text file uses consistent delimiters, Excel can split the data cleanly. If the structure is irregular or uses fixed-width columns (where data is padded with spaces to align), the process takes an extra step.

Method 1: Open the File Directly in Excel

The simplest starting point is just opening the .txt file from within Excel.

1. Open Excel and go to File → Open
2. Change the file type filter to All Files
3. Select your .txt file
4. Excel launches the Text Import Wizard

The wizard walks you through three steps:

• Step 1: Choose between Delimited or Fixed Width
• Step 2: Select your delimiter character(s)
• Step 3: Set the data format for each column (General, Text, Date, etc.)

This method gives you full control over how Excel interprets the data. It's particularly useful when columns contain leading zeros (like zip codes or ID numbers) — setting those columns to Text format in Step 3 prevents Excel from stripping the zeros.

Method 2: Use the "Get Data" / Power Query Import (Modern Excel)

In Excel 2016 and later (including Microsoft 365), the recommended approach is through Power Query:

1. Go to the Data tab
2. Click Get Data → From File → From Text/CSV
3. Select your file
4. Excel previews the data and auto-detects the delimiter
5. Click Load to import directly, or Transform Data to clean it first

Power Query is more powerful than the legacy wizard. It lets you:

• Remove unwanted rows or columns before loading
• Change data types in bulk
• Handle inconsistent formatting
• Set up a refreshable connection — if the source text file updates, you can refresh the Excel data without re-importing manually

This method is better suited for recurring workflows or larger datasets.

Method 3: Copy and Paste with Text to Columns

If the text file is small and you just need a quick result:

1. Open the text file in Notepad (or any text editor)
2. Select all and copy
3. Paste into Excel — all content lands in Column A
4. Select Column A, then go to Data → Text to Columns
5. Follow the same delimiter steps as the Import Wizard

This approach skips the file dialog entirely. It works well for one-off conversions where the data doesn't need to be re-imported later.

Method 4: Rename the File Extension (CSV Only)

If your text file is actually comma-separated, you can sometimes just rename the file from .txt to .csv. Excel recognizes .csv files natively and opens them without the import wizard — though it applies its own default parsing rules, which occasionally misreads columns.

⚠️ This shortcut can cause issues with columns that contain commas inside quoted strings, or with date and number formats that vary by locale. It's a quick fix, not always a clean one.

Variables That Affect Your Outcome

Common Problems and What Causes Them 🔍

All data appears in one column — the wrong delimiter was selected during import. Re-run the import and choose the correct separator.

Numbers show as dates — Excel auto-formatted a column. Fix by setting the column data type to Text or Number before finalizing the import.

Special characters appear garbled — a character encoding mismatch. In the import wizard or Power Query, change the file origin to UTF-8 or the encoding that matches your source file.

Leading zeros disappear — Excel treats those columns as numbers. Format them as Text during import to preserve the original values.

Fixed-Width Files: The Special Case

Some text files don't use a delimiter at all. Instead, every field occupies a fixed number of characters — for example, a name always takes up columns 1–20, a date always occupies columns 21–28, and so on. This format is common in legacy system exports and government data files.

For these, the Import Wizard's Fixed Width option lets you click to place vertical dividers between columns. Power Query handles this too, but requires slightly more manual configuration.

How Your Setup Changes the Right Approach

The cleanest method for a one-time personal import is often just the Text Import Wizard — it's straightforward and gives direct control. For anyone working with data regularly, pulling files from databases, or sharing workbooks with others, Power Query's connection-based approach scales much better and reduces manual steps over time.

Where it gets more situational is around file structure — a cleanly delimited export from a known source behaves very differently from a manually formatted text file, an unusual fixed-width export, or a file with mixed encodings. Your text file's specific structure, what you're doing with the data in Excel, and how often you need to repeat the process are the variables that determine which method actually fits your situation. 📊