How to Import a Text File into Excel (5 Methods)

Differences Between Text File and Excel File

1. File Format:
   • Excel files are binary files with a structured format, while text files are plain-text files.
2. Structure:
   • Text files have no preset structure; they consist of rows and columns separated by delimiters.
   • Excel files have a predefined structure with rows and columns arranged into spreadsheets.
3. Data Types:
   • Excel files support various data types (text, numbers, dates, formulas), whereas text files store data as text only.
4. Features:
   • Excel files offer sorting, filtering, calculations, and charting capabilities.
   • Text files are straightforward and lightweight.
5. File Size:
   • Text files are smaller because they lack formatting or styling data.
6. Compatibility:
   • Any text editor can read and edit text files, but Excel files require Microsoft Excel or compatible spreadsheet tools.

When to Import a Text File to Excel

Consider importing a text file into Excel when:

• Connecting with external programs that export reports as text files.
• Transferring pre-existing data from a text file to Excel.
• Manipulating data using Excel’s powerful tools for calculations and organization.

In conclusion, importing text files into Excel streamlines processes, saves time, and allows you to leverage Excel’s capabilities for efficient data manipulation and analysis.

Dataset Overview

Let’s consider the “Age and Gender Distribution of Tenants” dataset for a specific apartment. This dataset includes information such as Name, Age, and Gender of tenants, organized under columns B, C, and D.

We have the dataset in (.txt) format, which we’ve opened in the Notepad application. Additionally, we’ve displayed the same dataset opened in Excel, allowing us to observe the differences in visualization and formatting.

Note: This example uses a basic dataset for simplicity. In practical scenarios, you may encounter larger and more complex datasets.

Now, let’s proceed to explore the methods for importing this text file into Excel.

Method 1 – Import Text File into Excel with Columns

• Open Excel and create a new workbook.
• Navigate to the Data tab.
• Select Get Data and choose From Text/CSV.

• The Import Data window will appear.
• Locate and double-click the desired text file from the file explorer.

• Specify delimiter and data format options.
• Click Load to import the data into Excel.

• A dialog wizard Import Data will appear. Select Existing worksheet >> Select the cell $B$4. Press OK.

• Excel will import your text file to your existing worksheet.

Method 2 – Utilize File Tab to Import Text File to Excel

• Go to the File tab.

• Click Open and then select Browse.

• Choose the preferred text file in the Open window and click Open.

• In Step 1 of Text Import Wizard, check the box My data has headers and click Next.

• Excel will already have selected Tab as the delimiter; click Next.

• Click Finish to complete the task.

• A new workbook will be created to open the text file within Excel.

Method 3 – Use Text to Columns Wizard

• Select the entire dataset.
• Go to Data >> Text to Columns in the Data Tools group.

• In the Convert Text to Columns Wizard – Step 1 of 3, select the Delimited option and press Next.

• In Step 2 of 3, uncheck all tick marks except for Comma and Space. Preview your data in this window and press Next.

• In Step 3 of 3, select General and click Finish.

• Confirm replacing the old data in Column B if prompted.

The imported data will now be split into columns.

Method 4 – Use Copy and Paste Feature for Simplicity

• Open the text file in any software capable of reading plain text files (e.g., Notepad).
• Highlight the entire dataset you want to import.
• Press CTRL + C on your keyboard to copy the selected data.
• Open your Excel file where you want to import the data.
• Click on the cell where you want to paste the data.
• Press CTRL + V to paste the copied data.

Considerations:

• This method is straightforward and time-saving, especially for smaller datasets.
• However, keep in mind that it’s less efficient for larger datasets due to the following reasons:
  • Manual Process: Copying and pasting data manually can be repetitive and time-consuming, increasing the risk of errors (e.g., skipping rows or copying incorrect data).
  • Limited Capacity: Excel’s copy-and-paste feature has limitations on the amount of data that can be transferred at once. For large datasets, this may require multiple iterations.
  • Formatting Issues: Pasting text data directly may lead to formatting problems (e.g., losing leading zeros or altering data formats), affecting subsequent analysis in Excel.

Method 5 – Import Text File to Excel Using VBA Macro

• Open the Developer Tab:
  • Click on the “Visual Basic” button in the Code group to launch the Microsoft Visual Basic for Applications window.

• Insert a Module:
  • In the Visual Basic for Applications window, click the Insert tab.
  • Choose Module from the list to create a new module where we’ll insert our VBA code.

• Paste the VBA Code:
  • Below is the VBA code that accomplishes the task. Paste this code into the module:

Sub ImportTextFileToExcel()
Dim textFileNum As Integer
Dim rowNum As Integer
Dim colNum As Integer
Dim textFileLocation As String
Dim textDelimiter As String
Dim textData As String
Dim tArray() As String
Dim sArray() As String
Dim usedRange As Range

textFileLocation = "G:\Exceldemy\Age and Gender Distribution of Tenants.txt"
textDelimiter = vbTab
textFileNum = FreeFile

Open textFileLocation For Input As textFileNum
textData = Input(LOF(textFileNum), textFileNum)
Close textFileNum

tArray() = Split(textData, vbLf)

For rowNum = LBound(tArray) To UBound(tArray) - 1
If Len(Trim(tArray(rowNum))) <> 0 Then
sArray = Split(tArray(rowNum), textDelimiter)
For colNum = LBound(sArray) To UBound(sArray)
ActiveSheet.Cells(rowNum + 4, colNum + 2) = sArray(colNum)
Next colNum
End If
Next rowNum

With ActiveSheet.Range("B4:D4")
.Font.Bold = True
.Font.Size = 12
.Interior.Color = RGB(255, 221, 221) ' light gray fill color
End With

Set usedRange = ActiveSheet.Range("B4:D14")

' Apply borders to the used range
With usedRange.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

MsgBox "Data Imported Successfully", vbInformation

End Sub

• Run the Code:
  • Click the green play button or press F5 to run the code.
  • The result will be visible in your Excel sheet.

• After executing the code, the result can be seen below:

This VBA macro imports the text file, splits it into columns, formats the data, and provides a success message. Adjust the file location and delimiter as needed for your specific scenario.

How to Edit the Imported Text File in Excel

• Review Imported Data:
  • After importing the data, review it to ensure correctness and identify any errors or inconsistencies.
  • Check that the data is in the correct format, contains no missing values, and has no duplicate entries.
  • Verify that the data is placed in the correct cells after the import.
• Adjust Column Widths:
  • To make all data visible and easy to read, adjust column widths.
  • Click on the column header and drag it to the desired width.
  • Alternatively, use the AutoFit feature to automatically adjust column widths based on the content.
• Format Data as Needed:
  • Depending on the data type, apply formatting for readability or calculations.
  • Format numbers as currency, dates as specific date formats, and text as bold or italicized.
  • Access formatting options via the Format Cells dialog box or the formatting tools in the Home tab.
• Enhance Appearance:

• Bold column headers.

• Apply a background fill color.
• Slightly increase font size.
• Add borders to the data.

• Save the Excel File:
  • Click the Save button in the Quick Access Toolbar or select Save or Save As from the File menu.
  • Choose a meaningful file name and location for easy access.

How to Convert Text File to Excel Table

• Go to the Data tab on the ribbon.
• Select From Text/CSV under the Get & Transform Data group.

• Choose the Age and Gender Distribution of Tenants file and click Open.

• In the dialog box, select Transform Data.

• Click the Close & Load command.

• Excel will create a new worksheet with the data in a table format, using the sheet name from the text file.

How to Export Text File from Excel

Export Data to a Text File:

• Go to the File tab on the ribbon.

• Select Save As and then click Browse.

• In the Save As dialog box, choose Text (Tab delimited) as the save type.

• Click Save.
• The new text file will be saved in your preferred location.

Once you have exported the text file from Excel, you can open it in any program that supports the file format you chose (e.g. Notepad, Microsoft Word, or a database program).

Benefits of Importing Text File to Excel

Importing a text file into Excel offers several benefits for efficient data management and analysis. Let’s explore these advantages:

1. Data Manipulation: Excel provides powerful features for data manipulation. By importing a text file, you can easily sort, filter, and organize data, making it more accessible for analysis and insights.
2. Time-saving: Manually copying and pasting data from a text file to Excel can be time-consuming, especially with large datasets. Importing the text file streamlines the process and saves valuable time.
3. Reduced Errors: Importing data directly from a text file reduces the risk of errors that may occur during manual data entry.
4. Enhanced Data Analysis: Excel’s functions and formulas allow for complex calculations. By importing a text file, you can leverage these features to analyze data and uncover insights that might not be immediately apparent.
5. Compatibility: Text files can be created by various programs and operating systems. Importing data from a text file to Excel allows you to consolidate information from different sources and formats.
6. Scalability: Text files can handle large datasets efficiently. Importing them into Excel enables you to work with substantial amounts of data without memory or performance concerns.

Things to Remember

• Ensure the text file format is compatible with Excel (e.g., delimited or fixed-width).
• Specify the correct data type for each column during import.
• Use text qualifiers (like quotes) if your text file includes delimiter characters.
• Choose the appropriate encoding for non-ASCII characters.

Frequently Asked Questions

1. Can I import a text file with non-English characters into Excel?

Yes, Excel can import text files with non-English characters. Just select the right character encoding in the Text Import Wizard.

2. Can I import a text file with headers into Excel?

If your text file has headers, choose the My data has headers option in the Text Import Wizard.

3. Can I import a text file into a specific location in an Excel worksheet?

You can import a text file into a specific Excel worksheet location by selecting the starting cell and using the Text Import Wizard’s Existing Worksheet option.

Download Practice Workbook

You can download the practice workbooks from here:

Import Text File to Excel: Knowledge Hub

• Convert Text File to Excel Automatically
• Import Text File with Multiple Delimiters into Excel
• Convert Notepad to Excel with Columns
• Import Data from Text File into Excel
• VBA Code to Convert Text File to Excel

<< Go Back to Importing Data in Excel | Learn Excel