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

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.

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 and select the cell $B$4.
• Press OK.

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

Method 2 – Utilize the File Tab to Import a Text File into Excel

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

• In Step 1 of the Text Import Wizard, check the box My data has headers and 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 and select 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.

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

The imported data will now be split into columns.

Method 4 – Use the 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 a Text File into 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

        Code Breakdown

        • The first few lines define some variables that will be used later in the code.
        • The textFileLocation and textDelimiter variables are set to the location of the text file and the delimiter used in the file.
        • The textFileNum variable is set to the file number of the text file using the FreeFile function.
        • The text file is opened for input and its contents are read into the textData variable using the Input function. The file is then closed.
        • The textData variable is split into an array of lines using the vbLf (line feed) character and stored in the tArray() variable.
        • A loop is started to iterate over each line in the tArray() variable.
        • For each line, the Len function is used to check if the line has any characters in it (i.e. it is not blank). If the line is not blank, the line is split into an array of columns using the textDelimiter and stored in the sArray() variable.
        • Another loop is started to iterate over each column in the sArray() variable.
        • The ActiveSheet.Cells() function is used to set the value of the current cell in the Excel sheet to the corresponding value in the sArray() variable. The rowNum and colNum variables are used to keep track of the current row and column.
        • After all the data has been imported, the range of cells that the data was imported to is formatted to have the bold font, size 12, and light gray fill color.
        • The usedRange variable is set to the range of cells that the data was imported to.
        • The Borders property of the usedRange object is used to apply a thin continuous border to the cells.
        • A message box is displayed indicating that the data was imported successfully.
          

        

        • 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.
            • 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.
            • 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.

            

            • 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 a Text File into an 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.

              

              

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

              

              How to Export a Text File from Excel

              Export Data to a Text File:

              • Go to the File tab on the ribbon.

              

              

              • In the SaveAs 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 a Text File into 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: