How to Create an Excel File from Text Data

A plain text file is a universal format for data, but it’s not very useful for analysis. Turning that block of raw information into a structured, easy-to-use Excel spreadsheet is a fundamental skill that unlocks your ability to sort, filter, chart, and actually understand your data. This guide will walk you through the most effective methods to convert your text data into a proper Excel file, from the simple drag-and-drop to a more powerful, repeatable process.

First, Understand What You're Working With

Before importing anything, open your text file in a basic text editor (like Notepad on Windows or TextEdit on Mac). Look at how the data is structured. Most text files that contain data are organized in one of two ways, and recognizing the pattern is the first step.

The vast majority use a delimiter, which is just a fancy word for a character that separates one piece of data from the next. The most common you'll see are:

• Commas (,): This creates a Comma-Separated Values file, or a .csv.
• Tabs: This creates a tab-delimited file, often saved as a .txt.
• Semicolons (,), Pipes (|): These are also common in data exports from various systems.

For example, a .csv file of customer data might look like this:

Name,Email,SignUpDate,LastPurchase Jane Doe,jane.doe@email.com,2023-10-15,55.40 John Smith,john.s@email.com,2023-11-20,12.99 Sam Jones,sam.jones@email.com,2024-01-05,205.10

Each comma tells Excel where a new column should begin. Knowing which character your file uses will make the import process completely painless.

Method 1: Using the Text Import Wizard for Full Control

This is the most reliable and flexible method for turning text into perfectly formatted columns. It gives you precise control over how Excel interprets your data, which prevents common issues like ZIP codes turning into numbers (and losing their leading zeros).

Step 1: Open the Text File from Within Excel

Instead of double-clicking the file, start by opening Excel first.

1. Click on the File tab, then select Open.
2. Click Browse. The 'Open' dialog box will appear.
3. By default, Excel only looks for Excel files (.xlsx, .xls, etc.). You need to tell it to look for everything. Click the dropdown menu in the bottom-right corner (it usually says "All Excel Files") and change it to "All Files (.)" or **"Text Files (*.prn, *.txt, *.csv)"**.
4. Navigate to your text file, select it, and click Open.

This will launch Excel's Text Import Wizard, a simple three-step guide to get your data in order.

Step 2: Choose Your File Type

The wizard's first step asks you how your data is organized. You’ll see two options:

• Delimited: This means your data is separated by characters like commas, tabs, or spaces. For most .csv and .txt files, this is the one you want.
• Fixed width: This is less common. It’s for files where each column starts at a specific character position, using spaces as padding.

Select Delimited and click Next.

Step 3: Specify Your Delimiter

This is the most important step. Excel needs you to confirm which character separates your columns. You’ll see a list of common delimiters with checkboxes: Tab, Semicolon, Comma, Space.

Look at the Data preview window at the bottom of the wizard. As you check and uncheck the boxes, you’ll see the preview update in real-time. Your goal is to see clean vertical lines appear between your data, separating it into distinct columns.

• If your file is a standard .csv, check the Comma box.
• If your cursor moves in distinct chunks when you press the Tab key in your text file, check the Tab box.
• If your file uses something else, like a pipe (|), check the Other box and type that character into the field next to it.

Once the preview looks correct, click Next.

Step 4: Format Your Columns (Don't Skip This!)

The final step allows you to set the data format for each column before it's imported. By default, every column is set to "General," which lets Excel guess the format. This works most of the time, but it can cause problems.

A classic example is a column of ZIP codes. "General" format sees "07740" as a number and helpfully converts it to "7740," dropping the important leading zero. To prevent this, you can specify the data type here.

In the Data preview, click the header of the column you want to format. Then, select the correct data format from the options above.

• General: The default, for mixed numbers and text.
• Text: Choose this for any numerical data you don't plan to do calculations with, like IDs, phone numbers, or ZIP codes. This preserves the exact characters as they appear.
• Date: Use this for date columns, and select the correct MDY, DMY, or YMD format from the dropdown.
• Do not import column (skip): This lets you completely ignore a column you don't need.

Once you’ve set the formats for any tricky columns, click Finish. Your data will now appear in your Excel sheet, perfectly organized into clean rows and columns.

Method 2: Using "Get & Transform" (Power Query) for Repeatable Imports

If you regularly receive the same type of text file (like a weekly sales export or a daily analytics report) and have to repeat this import process, Excel's "Get & Transform Data" tools (also known as Power Query) are a game-changer. You set it up once, and then you can refresh the data with a single click.

Step 1: Go to the Data Tab

With a blank Excel workbook open, go to the Data tab on the Ribbon.

Step 2: Select "From Text/CSV"

On the far left of the Data tab, you'll see a group called "Get & Transform Data." Click the From Text/CSV button. This will open a window asking you to find and select your file. Do that and click Import.

Step 3: Preview and Adjust Settings

A preview window will now appear, showing a sample of your text data already formatted into columns. Power Query is usually smarter than the old wizard and makes a very good guess about the delimiter right away. However, you can change its settings if needed:

• File Origin: Usually fine as is, but if you see strange characters, you may need to adjust the character encoding.
• Delimiter: If Power Query guessed wrong, you can select the correct one from this dropdown menu.

Step 4: Load the Data

At the bottom of the window, you have a few options, but the main one you'll use is the green Load button.

Clicking Load will import the data directly into a new worksheet inside your Excel file. The best part? The data is automatically formatted as an Excel Table, which means you get instant access to sorting and filtering controls.

The Transform Data button, on the other hand, opens the Power Query Editor. This is a far more advanced tool where you can clean data before it even hits your spreadsheet by removing columns, splitting text, pivoting data, and much more. While it's incredibly powerful, for a simple text import, Load is all you typically need.

Why is This Method Special?

Because you've defined an import query, Excel remembers the steps. If the source text file gets updated with new data, you don't have to repeat the import process. Simply right-click anywhere in your data table and hit Refresh. Excel will automatically go grab the latest version of the file and update your table for you.

Last Step: Save Your Work as an Excel Workbook

No matter which method you used, your final step is crucial. You are currently viewing your imported data, but you haven't saved it as an actual Excel file yet.

Go to File > Save As. In the "Save as type" dropdown menu, change the format from "Text" or "CSV" to "Excel Workbook (*.xlsx)".

This ensures that any formatting, charts, formulas, or extra sheets you add will be saved. If you accidentally save it back as a .csv, all that hard work will be lost, leaving you with just the raw data again.

Final Thoughts

Learning how to properly import text data into Excel moves you from being a data spectator to a data user. With the Text Import Wizard and Power Query, you have both a quick tool for one-off tasks and a powerful, repeatable workflow for your routine reports, giving you more time for analysis and less for data preparation.

Manually pulling data from CSVs and text files is often just the beginning of a weekly reporting chore. As your sources of insight multiply, this process can get out of hand quickly. One moment you're importing a single .txt file, the next you're wrangling dozens of different exports from Google Analytics, Facebook Ads, Shopify, and your CRM every Monday morning. For those moments, we built Graphed_. It connects directly to all those platforms automatically, eliminating the need to ever download another CSV report. You can build live dashboards and get instant answers using plain English, freeing you from manual data prep so you can stay focused on growing your business.