The article will provide you with 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 after we convert it to an Excel spreadsheet.
How to Convert Text File to Excel Automatically: 3 Convenient Ways
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 the 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
- 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, put a Hyphen (–) in it and go Next.
- After that, click on Finish.
- Then you will see the data from the Text File appear in the current Excel File.
- 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 a 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
- 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.
- 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, it is hyphen (–).
- Select Each occurrence of the delimiter and click OK.
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.
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.
Read More: How to Import Text File with Multiple Delimiters into Excel
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
- 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.
- 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, it is hyphen (–).
- Select Each occurrence of the delimiter and click OK.
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.
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 the Text File to Excel table automatically.
Read More: VBA Code to Convert Text File to Excel
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.
Download Practice Workbook
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.
Related Articles
<< Go Back to Import Text File to Excel | Importing Data in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
