How to Convert Text File to Excel Automatically (3 Suitable Ways)

The article will provide you the basic methods on how to convert Text File to Excel automatically. Sometimes you may save your data in a Text File and later, you have to work with that data in Excel for analysis. For that reason, you are required to convert that Text File into an Excel spreadsheet.

In this article, we will convert the following Text File which we named Convert Text File to Excel. I have given here a preview of how this Text File will look like after we convert it to an Excel spreadsheet.

convert text file to excel automaticallly


Download Practice Workbook


3 Ways to Convert Text File to Excel Automatically

1. Opening the Text File Directly in Excel to Convert It to Excel File

The best way to convert a Text File into an Excel spreadsheet or file is to open the Text File directly from the Excel File. Let’s go through the process below.

Steps:

  • First, open an Excel File and then go to File Tab.

  • Then select the option Open from the green bar.
  • Select Browse. You will see the Open window appear.
  • Select the Text File from its location and click on Open in the Open
  • Make sure you have the option All Files

convert text file to excel automaticallly

  • After that, the Text Import Wizard will show up. As we separated our columns by a Delimiter (hyphens ()), we select Delimiter and go Next.

  • Check Other and put a Hyphen () in it and go Next.

convert text file to excel automaticallly

  • After that, click on Finish.

  • Then you will see the data from the Text File appear in the current Excel File.convert text file to excel automaticallly
  • The data you see is in a messy condition. So I formatted the text according to my convenience.

Thus you can convert Text File to Excel automatically.


2. Using Text Import Wizard to Convert Text File to Excel Automatically

Another way to convert Text File into an Excel is to apply the Text Import Wizard from the Data Tab. This operation will convert your Text File into an Excel Table. Let’s see what happens when we implement this method.

Steps: 

  • First, select Data >> From Text/CSV

convert text file to excel automaticallly

  • Then the Import Data window will show up. Select the Text File you want to convert from the location and click on Import. In my case, it is Convert Text File to Excel_1.

  • You will see a Preview Box. Just click on Transform.

convert text file to excel automaticallly

  • After that, you will see your data from the Text File in a Power Query Editor. Select Home >> Split Column >> By Delimiter

  • In the following window, you need to select the Delimiter on which these data from the Text File will split. In our case, its hyphen ().
  • Select Each occurrence of the delimiter and click OK.

convert text file to excel automaticallly

After that, you will see the data split in a convenient way.

  • To load this table in an Excel sheet, just click on Close & Load.

convert text file to excel automaticallly

And there you go, you will see the information from the Text File as a table in a new Excel sheet. You can format the table according to your convenience.

Thus you can convert Text File to Excel automatically.


3. Applying Get Data Wizard  to Automatically Convert Text File to Excel Table

You can also convert a Text File to Excel by using the Get Data Wizard from the Data Tab. This operation will also convert your Text File into an Excel Table. Let’s see what happens when we implement this method.

Steps: 

  • First, select Data >> Get Data >> From File >> From Text/CSV

convert text file to excel automaticallly

  • Then the Import Data window will show up. Select the Text File you want to convert from the location and click on Import. In my case, it is Convert Text File to Excel_1.

  • You will see a Preview Box. Just click on Transform.

convert text file to excel automaticallly

  • After that, you will see your data from the Text File in a Power Query Editor. Select Home >> Split Column >> By Delimiter

  • In the following window, you need to select the Delimiter on which these data from the Text File will split. In our case, its hyphen ().
  • Select Each occurrence of the delimiter and click OK.

convert text file to excel automaticallly

After that, you will see the data split in a convenient way.

  • To load this table in an Excel sheet, just click on Close & Load.

convert text file to excel automaticallly

And there you go, you will see the information from Text File as a table in a new Excel sheet. You can format the table according to your convenience.

Thus you can convert Text File to Excel table automatically.


Practice Section

Here, I’m giving you the data from the Text File so that you can make your own Text File and convert it to an Excel File on your own.


Conclusion

In a nutshell, you will learn all possible ways to convert Text File to Excel automatically after reading this article. This will save you a lot of time because otherwise, you might transfer the data from your Text File manually. If you have any other ideas or feedback, please share them in the comment box. This will help me enrich my upcoming article.

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo