To open a text file in Excel, you can simply open it using the open dialog box. Once you open it, Excel will show you the text-to-columns option to import data into the worksheet. If data is in tabular form, you just need a few clicks to add it to Excel.
In this tutorial, we will look at the three different ways to open a text file using Excel.
Steps to Open a Text File in Excel
- First, go to the file tab, click on open, and click on Browse.
- After that, in the browse dialog box, select “All Files” in the drop-down next to the file name.
- Next, click on the text file you want to open and click OK.
- You have the text-to-columns dialog box. Click on the next button to go to the next step, and checkmark “My data has headers.” If your data already has headers.
- In the next step, checkmark the “Tab”, and click Next.
- In the end, click Finish to get the data into the worksheet.
Once you click Finish, you’ll have the data in the sheet as we have below:
Once you open a text file in Excel, you’ll get the data, but it won’t save that text file as an Excel file. Save it as an Excel workbook, go to the File ⇢ Save As.
Open a Text File in Excel with Power Query
- Go to the Data ⇢ From File ⇢ From Text/CSV.
- Select the file that you want to import and click on the Import button.
- Click on the load button to load data in the worksheet.
- Now you have the data in the worksheet.
The best part of using the power query for importing data from the text file is, it’s dynamic. When you update data in the text file, you can just update it in Excel just by clicking on the refresh button.
Copy-Paste Data from Text File to Excel Worksheet
For this, open the text file and use the keyboard shortcut Ctrl + A to select all the data from the text file.
Go to the worksheet, select cell A1 and press the Ctrl + V to paste data there.