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 texts 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 explanation.
5 Methods to Convert Notepad to Excel with Columns
Assuming that Sales Report of some Product Items is given along with Product ID, Sates, and Sales in Notepad as shown in the below screenshot.
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.
➤ 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.
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.
➤ 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.
➤ Later (step 3 of 3), make sure that the column data format is General and click over the Finish button.
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.
Eventually, you’ll get the following output after changing the formatting based on your requirement.
Read More: How to Convert Text File to Excel Automatically (3 Suitable Ways)
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.
➤ Now, just go to the B4 cell and press CTRL + V.
So, the output will look as follows.
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.
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.
➤ For doing this, go to the Data tab > Data Tools ribbon > choose the Text to Columns feature.
➤ In steps 1 of 3, you have to choose the Delimited data type.
➤ Next (in steps 2 of 3), pick the Comma as Delimiters.
➤ In the last step, you have to keep the General data format checked.
Eventually, you’ll get the following output.
Read More: Convert Excel to Text File with Delimiter (2 Easy Approaches)
Similar Readings
- How to Extract Data from a List Using Excel Formula (5 Methods)
- How to Extract Data from Excel to Word (4 Ways)
- Extract Text After a Character in Excel (6 Ways)
- Excel Formula to Get First 3 Characters from a Cell(6 Ways)
- Return Multiple Values in Excel Based on Single Criteria (3 Options)
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.
➤ Click over the text file and then pick the Import button.
➤ 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.
➤ Next, specify the location (e.g. =PowerQuery!$B$4).
Finally, you’ll get the following output.
5. Using VBA Code
The fifth and the 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).
➤ Secondly, go to Insert > Module.
➤ 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
⧬ 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.
After using the Text to Columns feature discussed in step 2 of the third method and formatting, the above output will look as follows.
Read More: VBA Code to Convert Text File to Excel (7 Methods)
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 requirement. Anyway, don’t forget to share your thoughts.
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)
- Excel VBA: Pull Data Automatically from a Website (2 Methods)
- How to Import Data from Secure Website to Excel (With Quick Steps)
- How to Extract Data from Image into Excel (With Quick Steps)
- Extract Filtered Data in Excel to Another Sheet (4 Methods)