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.

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.

Import Text File into Excel with Columns

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

Import Text File into Excel with Columns through Opening file explorer

  • Then go to Load >> Load To.

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

importing data

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

how to import text file to excel

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.

Utilize File Tab to Import Text File to Excel

  • Then go to Open >> Browse.

open tab

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

Utilize File Tab to Import Text File to Excel

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.

Use Text to Columns Wizard

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

Convert to text to Columns Wizard

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

Convert Text to Columns Wizard

  • 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


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

Import Text File to Excel Using Excel VBA

  • 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

VBA code

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

Converting Text File to Excel Table to import text file to Excel

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

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

Converting Text File to Excel Table

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

Import text file in a new worksheet

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.

Practice Section


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

Khan Muhammad Kawser

Khan Muhammad Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo