Meraz Al Nahian
The article will provide you with the basic methods on how to open a Notepad or Text File in Excel with columns. Sometimes you may save your data in a Notepad or Text File and later, you have to work with that data in Excel for analysis. For that reason, you are required to open that Notepad or Text File to an Excel spreadsheet with columns.
In this article, we will open the following Notepad to Excel with columns which we named Open Notepad to Excel. Here is a preview of how this Text File will look like after we open it in Excel. We have information about some products in the dataset- their Names, Prices, and Quantities separated by hyphens (–) in the Notepad.
Download Practice Workbook
3 Ways to Open Notepad or Text File in Excel with Columns
1. Opening the Notepad or Text File Directly in Excel with Columns by Delimiter
The best way to open a Notepad or Text File in an Excel file with columns is to open the Notepad or Text File directly from the Excel File and then use delimiters to separate the data by columns. 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.
- Click on Browse. You will see the Open window appear.
- Select the Notepad or Text File from its location and click on Open in the Open
- Make sure you select All Files.
- After that, the Text Import Wizard will show up. As we separate our columns by a Delimiter (hyphens (–)), we select Delimiter and go Next.
- Select Other and type a Hyphen (–) in it and go Next.
- Later, click on Finish.
- Then you will see the data from the Notepad or Text File appear in the current Excel File. Also, you can see that these data appear in different columns
- My data appears to be a bit messy. So I formatted the text according to my convenience.
Thus you can open Notepad or Text File in Excel with Columns.
Read More: Excel VBA: Read Text File into String (4 Effective Cases)
2. Using Text Import Wizard to Open Notepad or Text File in Excel with Columns
Another way to open a Notepad or Text File in Excel is to apply the Text Import Wizard from the Data Tab. This operation will transfer the data of your Notepad or Text File into the Power Query Editor of Excel. 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 open from the location and click on Import. In my case, it is Convert Notepad to Excel.
- You will see a Preview Box that will show you how this data will appear in the Power Query Editor. As we want to separate the columns by a delimiter, click on Transform.
- After that, you will see the data of the Notepad or Text File in a Power Query Editor. Select Home >> Split Column >> By Delimiter
- In the following window, you have to select the Delimiter on which the data from the Text File or Notepad will split into different columns. In our case, it’s hyphen (–).
- Select Each occurrence of the delimiter and click OK.
- After that, you will see the data of your Text File split into different columns. To load this table in an Excel sheet, just click on Close & Load.
And there you go, you will see the information from the Notepad or Text File as a table in a new Excel sheet. You can format the table according to your convenience.
Thus you can open a Notepad or Text File in Excel with columns.
Read More: How to Import Text File to Excel Automatically (2 Suitable Ways)
Similar Readings
- Excel VBA to Import CSV File without Opening (3 Suitable Examples)
- How to Import CSV into Existing Sheet in Excel (5 Methods)
- Excel VBA: Import Comma Delimited Text File (2 Cases)
- How to Convert CSV to XLSX without Opening (5 Easy Methods)
3. Applying Get Data Wizard to Convert Notepad or Text File to Excel Table
You can also open a Notepad or Text File in 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 apply this method.
Steps:
- First, select Data >> Get Data >> From File >> From Text/CSV
- Then the Import Data window will show up. Select the Notepad or Text File you want to open from the location and click on Import. In my case, it is Convert Notepad to Excel.
- You will see a Preview Box that will show you how this data will appear in the Power Query Editor. As we want to separate the columns by a delimiter, click on Transform.
- After that, you will see the data of the Notepad in a Power Query Editor. Select Home >> Split Column >> By Delimiter
- In the following window, you have to select the Delimiter on which the data from the Text File will split in different columns. In our case, its hyphen (–).
- Select Each occurrence of the delimiter and click OK.
- After that, you will see the data of your Notepad or Text File split into different columns. 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 with columns. You can format the table according to your convenience.
Thus you can open a Notepad or Text File in Excel with columns.
Read More: How to Import Text File to Excel Using VBA (3 Easy Ways)
Practice Section
Here, I’m giving you the data from the Notepad so that you can make your own Notepad or Text File and open it in Excel File with columns on your own.
Conclusion
In a nutshell, you will learn all possible ways to open Notepad or Text File in Excel with columns after going through this article. This will save you a lot of time because otherwise, you might transfer the data from your Notepad or 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 Convert CSV to XLSX (4 Quick Methods)
- Excel VBA to Read CSV File Line by Line (3 Ideal Examples)
- How to Read CSV File in Excel (4 Fastest Ways)
- Excel VBA to Convert CSV File to XLSX (2 Easy Examples)
- How to Open CSV File in Excel with Columns Automatically (3 Methods)
- Open CSV File with Columns in Excel (3 Easy Ways)
