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

Dataset

Sometimes your dataset might be stored in text (.txt) format. In Windows, text files are usually accessed using Notepad, a dedicated text editor application owned by Microsoft. Let’s explore how to convert from Notepad to Excel with columns. To demonstrate, we’ll use a Sales Report text file, which contains some Product Items along with Product ID, States, and Sales. Note: The above text is tab-delimited, which means that the tab is acting as the separator.

Method 1 – Opening Notepad Directly

Step 1 – Open Notepad ➤ Create a blank workbook and click File > Open. Opening Notepad Directly➤ Navigate to the file location where the text file is located. ➤ Click on the text file. If you don’t see the file, make sure that Text Files is selected as the Format. ➤ Click Open. Opening Notepad DirectlyStep 2 – Using the Text Import Wizard A dialog box namely Text Import Wizard opens. ➤ Check the Delimited option. ➤ Check My data has headers. ➤ Click Next. Opening Notepad Directly➤ Select Tab under Delimiters. ➤ Click Next. Opening Notepad Directly➤ Select General as Column data format. ➤ Click Finish. Opening Notepad DirectlyThe text file is imported into Excel, with the data accurately transcribed into Columns. How to Convert Notepad to Excel with Columns Opening Notepad DirectlyAfter changing the formatting as desired, the output is as follows: How to Convert Notepad to Excel with Columns Opening Notepad DirectlyRead More: How to Convert Text File to Excel Automatically

Method 2 – Copy and Paste Text from Notepad

The downside of Method 1 is the inability to change the location in the worksheet where the imported data will be placed. This Method allows the location of the imported data to be specified, for example, starting from cell B4. STEPS: ➤ Open the text file in Notepad. ➤ Select the text to be imported and press CTRL + C to copy. How to Convert Notepad to Excel with Columns Copy and Paste Text inside Notepad➤ In the Excel worksheet, click cell B4 and press CTRL + V to paste. Copy and Paste TextThe output is as follows: Copy and Paste Text

Method 3 – Import Comma Delimited Text from Notepad

One of the drawbacks of the Method 2 is that it doesn’t split into columns if the text uses a comma delimiter. Here’s how to import comma delimited text: Step 1 – Copy and Paste the Texts ➤ Open the text file in Notepad. ➤ Select and copy the text to be imported. How to Convert Notepad to Excel with Columns When the Text is Comma Delimited➤ Paste the copied text into cell B4 as in Method 2. ➤ The text is imported but without separation into columns. Step 2 – Use Text to Columns Feature ➤ Click Data > Data Tools > Text to Columns. A dialog box opens. How to Convert Notepad to Excel with Columns When the Text is Comma Delimited➤ Select the Delimited data type. ➤ Click Next. When the Text is Comma Delimited➤ Select Comma under Delimiters. ➤ Click Next. How to Convert Notepad to Excel with Columns When the Text is Comma Delimited➤ Keep the General data format checked. ➤ Click Finish. When the Text is Comma DelimitedThe imported text is split into Columns. How to Convert Notepad to Excel with Columns When the Text is Comma Delimited

Method 4 – Using Power Query

Power Query (a data transformation and preparation engine in Excel) is another way of easily importing text data into Excel. STEPS: ➤ Click the Data tab > Get Data > From File > From Text/CSV. How to Convert Notepad to Excel <a href=with Columns Using Power Query" width="637" height="516" />➤ Click the text file. ➤ Click the Import button. How to Convert Notepad to Excel <a href=with Columns Using Power Query" width="669" height="470" />➤ A preview of the output appears, where the Tab is selected as the Delimiter by default. ➤ Select the Load to option to specify a specific import location. Using Power Query➤ Specify the location (e.g. =PowerQuery!$B$4). ➤ Click OK. Using Power QueryThe text is imported separated into Columns. How to Convert Notepad to Excel <a href=with Columns Using Power Query" width="585" height="463" />

Method 5 – Using VBA Code

STEPS: ➤ Open a VBA module by clicking Developer > Visual Basic (or pressing ALT + F11). How to Insert VBA Code➤ Go to Insert > Module. How to Insert VBA Code➤ 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:

After running the code (press F5), the following output is returned:

How to Convert Notepad to Excel <a href=with Columns Using VBA Code" width="480" height="443" />

After using the Text to Columns feature detailed in step 2 of Method 3 and formatting, the above output will display as follows:

Using VBA Code

Download Practice Workbook