How to Import Text File to Excel (4 Easy Ways)

It is very important to import text files to Excel when you deal with a lot of data. There are various procedures to import text files to Excel. In this tutorial, we are going to show you 4 easy and simple ways how to import text file to Excel.

Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.

4 Methods to Import Text File to Excel

Let’s assume a dataset of student attendance. We want to import a text file into the dataset. For this, we have covered some easy methods. Here is an overview of our dataset.

Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.

1. Import Text File into Excel with Columns

For importing text files with columns in Excel, we will use the From Text/CSV command under the Data tab. But first, you have to create a text file that you want to import. Then you have to follow the steps.

📌 Steps:

• First of all, select the cell where you want to import the file. Here we select B11, then go to Data >> Get Data >> From Text/CSV.

• After that, a text file named Attendance_Sheet will appear. Then go to Import.

• Then go to Load >> Load To.

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

• Finally, your text file will be imported to your existing worksheet.

Note: In the method, we imported the text file into the existing sheet, however, you can import the file into a new working sheet if you want.

Read More: Importing Data into Excel (3 Suitable Ways)

2. Utilize File Tab to Import Text File to Excel

We can also import text files from the File tab option. It is an easy task where you need to select the desired text file through opening File Explorer. For doing this, follow the instructions stated below.

📌 Steps:

• Initially, select File tab.

• Then go to Open >> Browse.

• Hence, an Open window from File Explorer will appear in front of you. From that window, firstly, select All Files. Secondly, select a text file named Attendance_Sheet.txt. Lastly, press the Open option.

• Finally, a sheet with the corresponding name will appear in your Excel Sheet just like below.

Read More: How to Import Data from Text File into Excel (3 Methods)

3. Use Text to Columns Wizard

Microsoft Excel has a command called Text to Columns wizard under the Data tab. It is used to split the text into other columns. While using this wizard, you can define the delimiter character. Follow the simple steps to do that.

📌 Steps:

• Firstly, go to Data >> Text to Columns.

• After that, a dialog box will pop out named Convert Text to Columns Wizard – Step 1 of 3 >> select the Delimited option >> press Next.

• Then under the Convert Text to Columns Wizard – Step 2 of 3, uncheck all the tick marks except Comma and Space. 
• Press Next.

• Then, under the Convert Text to Columns Wizard – Step 3 of 3, select General >> then Finish

• Finally, the imported data has been split into columns.

Read More: How to Convert Notepad to Excel with Columns (5 Methods)

Similar Readings

• How to Extract Data From Table Based on Multiple Criteria in Excel
• Transfer Data from One Excel Worksheet to Another Automatically
• How to Extract Data from Website to Excel Automatically
• Return Multiple Values in Excel Based on Single Criteria (3 Options)
• How to Import Data from Word to Excel (3 Easy Methods)

4. Import Text File to Excel Using Excel VBA

We can also import the text file to Excel by using VBA code. Excel VBA code can add the text file and split it into your desired section.

📌 Steps:

• Firstly, go to Developer >> select Visual Basic

• A dialog box will pop out. Select Insert >> Module >> Module1.

• Write down the VBA code.

Sub ImportTextFileToExcel()

    Dim textFileNum, rowNum, colNum As Integer

    Dim textFileLocation, textDelimiter, textData As String

    Dim tArray() As String

    Dim sArray() As String

    textFileLocation = "G:\Exceldemy\Attendance_Sheet1.txt"

    textDelimiter = ","

    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

    MsgBox "Data Imported Successfully", vbInformation

End Sub

• Finally, the output will look like the image shown below.

In this method, we discussed the process of importing text files to Excel from a specific file location. Apart from this, you may also import text files using the File Selection option and considering Multiple Delimiters utilizing VBA.

Read More: How to Import Text File with Multiple Delimiters into Excel (3 Methods)

Converting Text File to Excel Table

You may also import the text files to an Excel table using some easy steps. Luckily, you’ll get the Sheet name automatically as it exists in the existing text file on our pc.

📌 Steps:

• Firstly, we have to select the From Text/CSV command under the Data option in the ribbon.

• After that, select the file named Attendance_Sheet. Select Import.

• A dialog box will pop out. There we need to select Transform Data.

• After that, select the data table and go to Close & Load command.

• Finally, it will open a new worksheet in your existing excel file and your text documents will be restored in the worksheet. In the following image, you see the Sheet name is Attendance_Sheet which is mainly the text file name.

Read More: VBA Code to Convert Text File to Excel (7 Methods)

Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Conclusion

That’s all about today’s session. And these are some easy methods on how to import text files to Excel. Please let us know in the comments section if you have any questions or suggestions. For your better understanding, please download the practice sheet. Visit our website, ExcelDemy, a one-stop Excel solution provider, to find out about diverse kinds of Excel methods. Thanks for your patience in reading this article.

Related Articles

• How to Import Data into Excel from Another Excel File (2 Ways)
• Excel VBA: Pull Data Automatically from a Website (2 Methods)
• How to Convert Text File to Excel Automatically (3 Suitable Ways)
• Excel Macro: Extract Data from Multiple Excel Files (4 Methods)
• How to Extract Data from Cell in Excel (5 Methods)
• Extract Text After a Character in Excel (6 Ways)
• How to Extract Specific Data from a Cell in Excel (3 Examples)