Import Text Data (Delimited) to Excel

In this tutorial, I will demonstrate the "Import Text Data" facility of Excel. This facility is useful when you have text file and you wish to get its data into excel cells. There are two ways, on how you can import the data to excel,

• Fixed Length
• Delimited

This tutorial will demonstrate how to import text data from a delimited file. As you might be aware that Excel automatically open CSV (comma delimited or tab separated) file in a readable format. However sometimes there might be a special delimiter in the file e.g '%' which Excel might not recognize automatically . One example is below.

As you can see that each data in the above file is separated by '%'. Now, lets say, you wish to import this data to the following sheet.

You can copy paste from file to each excel cell and accomplish the task. However this may not be a short task if the text file has hundreds and thousands of lines in it. Luckily Excel has a facility to do that for you. To avail that, go to "Data" tab and choose "From Text"

You will see a file open dialog. Locate the text file from which you wish to import the data and Click on "Import"

You will see a dialog like the one below. It will also show the first few lines from the text data file.

Check the delimited radio-button (if not already), and click on "Next".  You will see another dialog as below

Check the "Other" check-box and type "%" in it.

Now click on "Next". You will see a dialog like the one below

You can select the format of each column if you wish. One you are done with, click on "Finish". You will see something like the below dialog

You can select the first destination cell on this which in this case is cell A2. One you click on "OK" you will see the required result in the sheet.