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.
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
- 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.
- 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.
Read More: How to Convert Notepad to Excel with Columns (5 Methods)
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
- 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, its 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: Convert Excel to Text File with Delimiter (2 Easy Approaches)
Similar Readings
- How to Extract Year from Date in Excel (3 Ways)
- How to Extract Month from Date in Excel (5 Quick Ways)
- Extract Text After a Character in Excel (6 Ways)
- Excel Formula to Get First 3 Characters from a Cell(6 Ways)
- How to Pull Data From Another Sheet Based on Criteria in 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, its 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 Text File to Excel table automatically.
Read More: VBA Code to Convert Text File to Excel (7 Methods)
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.
Related Articles
- How to Import Data into Excel from Another Excel File (2 Ways)
- How to Import Text File with Multiple Delimiters into Excel (3 Methods)
- Extract Filtered Data in Excel to Another Sheet (4 Methods)
- How to Extract Data from Image into Excel (With Quick Steps)
- How to Convert Excel to Text File with Pipe Delimiter (2 Ways)
- Return Multiple Values in Excel Based on Single Criteria (3 Options)