How to Open a TXT File in Excel Using the Text Import Wizard

How to Open a TXT File in Excel: The Native Methods

Opening a plain text file (.txt) in Microsoft Excel is straightforward when the data is formatted cleanly, but it often confuses users when column separators are inconsistent or when newer versions of Excel change the default import interface.

Depending on your version of Excel, there are two native methods to import text files:

Method A: The Classic 3-Step Text Import Wizard (Legacy)

For decades, Excel featured the classic Text Import Wizard. While Microsoft hidden it behind advanced options in recent releases of Microsoft 365, it remains popular because of its simplicity.

Step 1: Choose Data Type
Open Excel, navigate to File → Open, change the file type filter from "Excel Files" to "Text Files (*.prn, *.txt, *.csv)", and select your file. The wizard asks whether your data is:

• Delimited: Characters such as commas or tabs separate each field.
• Fixed width: Fields are aligned in columns with spaces between each field.

Step 2: Set Delimiters
If you chose Delimited, check the delimiter used in your file (Tab, Semicolon, Comma, Space, or Other). A preview window at the bottom shows where the vertical column divider lines will be placed.

Step 3: Column Data Formats
This crucial step allows you to define the data type for each column (General, Text, Date, or Do Not Import). Setting sensitive fields (like Social Security numbers, ZIP codes, or inventory SKUs) to Text prevents Excel from stripping leading zeroes.

Method B: Modern Excel's Power Query Importer (From Text/CSV)

In Microsoft 365 and Excel 2019/2021, the default tool for importing text files is Power Query:

1. Click the Data tab in the ribbon.
2. Click From Text/CSV.
3. Select your file and click Import.
4. Excel analyzes the first 200 rows and displays a preview window. You can adjust the file origin encoding, delimiter, and data type detection.
5. Click Load to place the data on a new worksheet, or Transform Data to open the Power Query editor for advanced data cleaning.

Why Excel's Native Import Often Fails on Messy Data

While Excel's native tools are capable, they make automated assumptions that frequently cause issues with unformatted data:

• Dropped Leading Zeroes: Excel automatically converts strings of digits (such as 00521) to numeric integers (521).
• Numbers With Commas Split Apart: In CSV files, values like "$1,200.00" can be split into two separate cells ($1 and 200.00) if quotes are missing or malformed.
• Mixed-Spacing Breakage: Tables copied from terminal sessions or PDFs that rely on varied space runs often create dozens of extraneous empty columns in Excel.
• Slow Multi-Step Interface: When you just want to copy 10 rows from an email into your spreadsheet, walking through a 3-step dialog or Power Query window takes unnecessary time.

Example: The Leading Zero & Currency Trap in Native Import

Consider this simple product record in a raw text file:

Postal Code	Store ID	Total Sales
01234	0089	$1,420.50
02138	0094	$2,850.00

When to Use Excel Native vs. When to Use TextToTable