How to Import a TXT File into Excel and Format Data
Question details
The user needs to import plain text data from a TXT or CSV file into an Excel spreadsheet and properly separate and format it into columns.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Transferring raw data from text files into a structured spreadsheet format for analysis, reporting, or easier viewing.
- Observed behavior
- The raw text needs to be parsed using delimiters or fixed widths to fit accurately into Excel cells and column formats.
Before importing, open your TXT file in a standard text editor (like Notepad) to identify which delimiter (such as a comma, tab, or semicolon) separates your data fields.
Use the Text Import Wizard in Excel
The most straightforward method to open and parse a standard TXT file into structured Excel columns.
Excel's built-in Text Import Wizard allows you to define exactly how your text file should be split into columns by specifying delimiters and data types for each section.
In Excel, click on 'File' > 'Open' and click 'Browse' to locate your file.
In the Open dialog box, change the file type dropdown menu in the bottom right corner from 'All Excel Files' to 'Text Files (*.prn;*.txt;*.csv)'.
Select your TXT file and click 'Open'. The Text Import Wizard will launch automatically.
Select 'Delimited' if your data is separated by characters like commas or tabs, or 'Fixed width' if fields align in columns with spaces between each field. Click 'Next'.
Check the box corresponding to your file's delimiter (e.g., Tab, Comma, Space). Click 'Next', select the data format (General, Text, Date) for each column, and click 'Finish'.
Import using Power Query
Best for data that requires repeated cleaning or when you need to frequently import updated TXT files with the same structure.
Import TXT Files Easily with WPS Office
WPS Spreadsheet provides a highly intuitive Import Data feature that allows you to seamlessly open, parse, and format your text and CSV files into organized tables without any hassle.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing Spreadsheet document.
- 2. Access Data Import: Navigate to the 'Data' tab on the top ribbon, click on 'Import Data', and select 'Import Data' from the dropdown to browse for your TXT file.
- 3. Select Data Source: Choose the TXT file from your local storage and proceed to the Text Import dialog.
- 4. Define Delimiters: Select the appropriate delimiter (such as Tab, Comma, or Space) that separates your text data into logical columns.
- 5. Format and Finish: Assign specific data types to each column (e.g., Text, Date) in the preview window and click 'Finish' to successfully import the data.
Frequently Asked Questions
Why is all my imported text crammed into a single column?
This happens when the wrong delimiter is selected during the import process. To fix this, re-import the file and ensure you check the correct box (like Comma, Tab, or Space) in the Text Import Wizard that matches how your raw data is separated.
Can I change the formatting of the imported data later?
Yes. Once the data is imported into your spreadsheet, you can select any column and use the standard formatting tools on the Home tab to change number formats, adjust text alignment, or apply date formatting.
What is the difference between importing a TXT and a CSV file?
A CSV (Comma-Separated Values) file specifically uses commas to separate data fields and can often be opened directly by double-clicking it. A TXT file might use any character (like tabs, spaces, or pipes) as a delimiter and usually requires an Import Wizard to parse correctly. Both can be imported using the same tools in Excel.
How do I split text into columns if I already pasted it into Excel?
If you already pasted the data into a single column, you don't need to re-import. Select the column containing the data, go to the 'Data' tab, and click 'Text to Columns'. This will launch a wizard similar to the import process to help you split the data.
