How to Import Data from Text File to Excel

In this lesson, you will learn how to import data from a text file. As a example I will import lyrics of Justin Bieber's Love me song.

Get data

On the Data ribbon, the first section is called Get External Data:

from text ribbon

The third command from the left: From Text allows you to import data from a text file. After its use, the window appears in which you specify the file to import.

Import data

Then start the Text Import Wizard:

In the first step of the wizard, specify the type of the file:

  • Is it a delimited file? The following columns are displayed with a sign (e.g. space, comma, or tab). When Excel finds such a character in the line of the file, it knows that more data is entered in the next column.
  • Is it a file with a fixed width? In this file, the next column takes up a certain number of characters. For example, the first column has ten characters and the other eight. After entering the first column of ten characters, they typed to the next eight and so on.

Sometimes, the data in the file are preceded by a header. The system generates a text file that can contain the data's generation date, name, or other comments. The Start import at row allows you to bypass this information and start downloading data from the right place.

The file origin box is usually left unchanged. If the file in the preview below, instead of letters, you can see incomprehensible characters, you need to change this option.

Text Import Wizard Step 1 of 3

Click Next to move on to the second step of the wizard:

In this step, you specify which character separates the columns in the file. The default is a tab. Below you can see a preview of the imported file. The data is already in its columns. So I press on.

If you choose pre-delimited text (as in this example), this is where you can specify which columns are separated by a text file. In this case, it is a space.

Text Import Wizard Step 2 of 3

In the third step, just choose the data format of the imported data. I chose Text.

Text Import Wizard Step 3 of 3

Next, Excel will ask you where to paste the text. I want to import it into an existing worksheet beginning with the A1 cell.

Import Data

Excel imported text. Every word is in a separate cell as I wanted to do.

Imported Justin Bieber Love Me Lyrics

Now I can change every word of this "song" into a better one.

 

Tip! There is some other way to import text files to Excel. Try changing the extension of the file from txt to xls. It sometimes works.

Further reading: 
Import data from Access
How to import xlam?
How to Get Data from another Sheet?