How to Import Text Files to Excel

Managing massive volumes of data is a tricky task, especially when those are on your text files. Moving data to Excel for better calculations and presentations is an efficient way to handle such data. You don’t need to enter all data manually; Microsoft Excel has features that allow you to move all text data into an Excel spreadsheet without losing information. The article covers two Excel options to import text files to excel to ensure better data management and analysis.

How to Import a Text file into an Excel file?

There are two ways by which you can import text files to excel. 

• Open the data in Excel 
• Import text files as external data

Open the Data in Excel

To ensure better data segregation in Excel, you must ensure that the files are comma-separated files (.csv) or tab-separated files (.txt). You can easily open a text file as an Excel sheet using the Open command. This will not change the file format, and the file will retain its text file name extensions like .txt or .csv. 

• Go to the File option in the Ribbon and open the location of the text file.

• Pick the Text file of your choice in the dropdown of the dialog box.

• Choose the file you want to open and double-click it. 

If the file is a text file, Excel will launch its Text Import Wizard- Step 1 of 3″ dialog box. In this step, you need to select the original data type. If you use a separator between the imported text document data and the data, select the separator; if it uses space, choose a fixed width.

In Step 2 – Select Tab, you will see that your content is segregated. 

Step 3 – Click General.

After you are done, click Finish to complete the import.

You can also check Text Import Wizard for information about delimiters and advanced options.  

Import Text Files as External Data

• To import a text file, open an Excel spreadsheet on your computer and create a new spreadsheet.
• In the spreadsheet window, click the “Data” tab on the top Excel ribbon
• Click on the “Data” tab
• Hover on the “Get External Data” section and select Get Data > From File> From Text.

Must Explore – MS Excel Courses

• Click Get Data > From File > From Text.

• Click on “Import” to link the file to Excel.

Excel will open a “Power Query Editor” window. Here, you will decide how to load the text data into your spreadsheet.  

• Select Load to load the text data to your spreadsheet
• To choose which columns to keep in the spreadsheet, click the double arrow icon next to “column1”.

Choose the columns you need to keep and uncheck the “Use original column name as prefix” option. Click “OK.”

Points to Remember –

Delimiter – To present your data correctly, you must choose your delimiter. The data in this blog uses tabs to separate cells, so I select Tab. Choose that option if your spreadsheet uses spaces or semicolons to differentiate between cells. If you want to split the data at another character, you can enter that character in the Other: box.

Treat consecutive delimiters – Treat consecutive delimiters as one box does exactly what it says; in the case of commas, having two commas in a row would create a single new cell. Excel will create two new cells by default when you uncheck the box.

Text qualifier box – The text qualifier box is essential; when the wizard imports a spreadsheet, it will treat some of the cells as numbers and some as text. 

The character in this box will tell Excel which cells should be treated as text. Usually, there will be quotes (” “) around the text, so this is the default option, text qualifiers will not display in the final spreadsheet. You can also change it to single quotes (“) or none at all, in which case all quotes will remain in place when imported into the final spreadsheet.

Read MS Excel Tutorials

I hope this article on importing text into Excel will help you analyze your data.

Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel