How to Open Notepad or Text File in Excel with Columns (3 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

how to open notepad or text file in excel with columns


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.

how to open notepad or text file in excel with columns

  • 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.

how to open notepad or text file in excel with columns

  • 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

how to open notepad or text file in excel with columns

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

how to open notepad or text file in excel with columns

  • 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.

how to open notepad or text file in excel with columns

  • 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.

how to open notepad or text file in excel with columns

  • 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.

how to open notepad or text file in excel with columns

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


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

how to open notepad or text file in excel with columns

  • 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.

how to open notepad or text file in excel with columns

  • 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.

how to open notepad or text file in excel with columns

  • 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.

how to open notepad or text file in excel with columns

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

Meraz Al Nahian
Meraz Al 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo