How to Convert Notepad to Excel with Columns (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes your dataset might be stored in text (.txt) format in Notepad, a dedicated text editor application owned by Microsoft. However, Excel provides the opportunity to import the dataset from a wide range of sources. More importantly, Excel converts the text along with creating separate columns. In this instructive session, I’ll present 5 methods on how to convert Notepad to Excel with columns with proper explanations.


How to Convert Notepad to Excel with Columns: 5 Easy Methods

Assuming that the Sales Report of some Product Items is given along with Product ID, States, and Sales in Notepad as shown in the below screenshot.

Dataset

Note: The above text is tab-delimited. That means the tab is acting as the separator.

Now, you need to convert the texts in Notepad to Excel with Columns.


1. Opening Notepad Directly 

In the beginning method, I’ll show you the process of opening Notepad directly.

Step 01: Opening Notepad First

➤ Initially, you have to create a blank workbook and go to File > Open.

Opening Notepad Directly

➤ Then, go to the file location where you store the file (Notepad) as shown in the following image.

➤ After doing that, click over the text document. If you don’t find the file, make sure that you choose the format as Text Files (from the lower-right side).

➤ Lastly, press over the Open option.

Opening Notepad Directly

Step 02: Dealing with Text Import Wizard

Immediately (after opening the text file), you’ll see a dialog box namely Text Import Wizard (it will be opened by default). It is a 3-step process.

➤ Firstly (step 1 of 3), keep the circle checked before the Delimited data type and also check the box before the My data has headers option.

Opening Notepad Directly

➤ Now, you’re in step 2 of 3 of the Text Import Wizard. As the dataset is tab-delimited, you have to pick the Tab as Delimiters.

Opening Notepad Directly

➤ Later (step 3 of 3), make sure that the column data format is General and click over the Finish button.

Opening Notepad Directly

After doing that, you’ll get the following output. Astonishingly, the name of the workbook and sheet will be as it is in the text file.

How to Convert Notepad to Excel with Columns Opening Notepad Directly

Eventually, you’ll get the following output after changing the formatting based on your requirements.

How to Convert Notepad to Excel with Columns Opening Notepad Directly

Read More: How to Convert Text File to Excel Automatically


2. Copy and Paste Text from Notepad

The first method is surely a fast method but you cannot convert Notepad at a specific location. For example, if you want to store the dataset starting from the B4 cell, you may follow this method.

➤ Initially, select the texts after opening the Notepad and press CTRL + C to copy.

How to Convert Notepad to Excel with Columns Copy and Paste Text inside Notepad

➤ Now, just go to the B4 cell and press CTRL + V.

Copy and Paste Text

So, the output will look as follows.

Copy and Paste Text


3. Convert Notepad to Excel When Text Is Comma Delimited

One of the serious drawbacks of the second method is that it doesn’t work well if the text is available including a comma delimiter as depicted in the following screenshot.

Step 01: Copy and paste the Texts

➤ Primarily, you have to select and copy the texts.

How to Convert Notepad to Excel with Columns When the Text is Comma Delimited

After copying and pasting the texts in the B4 cell (as done in the second method), you’ll get the following output from the B4 to B15 cells.

Step 02: Use Text to Columns Feature

Now, you need to utilize the Text to Columns feature to create separate columns.

➤ To do this, go to the Data tab >  Data Tools ribbon > choose the Text to Columns feature.

How to Convert Notepad to Excel with Columns When the Text is Comma Delimited

➤ In steps 1 of 3, you have to choose the Delimited data type.

When the Text is Comma Delimited

➤ Next (in steps 2 of 3), pick the Comma as Delimiters.

How to Convert Notepad to Excel with Columns When the Text is Comma Delimited

➤ In the last step, you have to keep the General data format checked.

When the Text is Comma Delimited

Eventually, you’ll get the following output.

How to Convert Notepad to Excel with Columns When the Text is Comma Delimited


4. Power Query to Convert Notepad to Excel

While converting Notepad to Excel with columns, Power Query (a data transformation and preparation engine in Excel) will give you outstanding output.

➤ Firstly, go to the Data tab > drop-down list of the Get Data option > From File > From Text/CSV.

How to Convert Notepad to Excel with Columns Using Power Query

➤ Click over the text file and then pick the Import button.

How to Convert Notepad to Excel with Columns Using Power Query

➤ Then, you’ll see a preview of the text file where the Tab is fixed as the Delimiter automatically.

➤ Furthermore, if you want to load the converted data into a working sheet, select the Load to option.

Using Power Query

➤ Next, specify the location (e.g. =PowerQuery!$B$4).

Using Power Query

Finally, you’ll get the following output.

How to Convert Notepad to Excel with Columns Using Power Query


5. Using VBA Code

The fifth and last method is about the application of VBA code to convert Notepad to Excel with columns with a single click.

Before doing that you need to create a module to insert the VBA code.

➤ Firstly, open a module by clicking Developer > Visual Basic (or press ALT + F11).

How to Insert VBA Code

➤ Secondly, go to Insert > Module.

How to Insert VBA Code

➤ Then, copy the following code into the newly created module.

Sub ConvertNotepadToExcel()
Dim Txt As String
Open "E:\Exceldemy\Sales Report.txt" For Input As 60
Range("B4").Select
Do Until EOF(60)
Input #60, Txt
ActiveCell.Value = Txt
ActiveCell.Offset(1, 0).Select
Loop
Close (60)
End Sub

Using VBA Code How to Convert Notepad to Excel with Columns

⧬ Two things that you have to change:

  • Specify the path: Certainly, you have to specify the path (file location) of the existing text file e.g. E:\Exceldemy\Sales Report.txt
  • Select the output cell: Then, you have to specify the location where you want to get the converted data e.g. B4 cell.

After running the code (keyboard shortcut is F5), you’ll get the following output.

How to Convert Notepad to Excel with Columns Using VBA Code

After using the Text to Columns feature discussed in step 2 of the third method and formatting, the above output will look as follows.

Using VBA Code

Read More: VBA Code to Convert Text File to Excel


Download Practice Workbook


Conclusion

That’s the end of today’s session. This is how you can convert Notepad to Excel with columns. Now, choose any method based on your requirements. Anyway, don’t forget to share your thoughts.


Related Articles

<< Go Back to Import Text File to Excel | Importing Data in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo