How to Import Text Files to Excel

How to Import Text Files to Excel

3 mins read1.9K Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Dec 8, 2023 13:23 IST

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 Text in Excel

 

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

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.  

Microsoft Excel vs. Google Sheets: Shiksha Online
Microsoft Excel vs. Google Sheets: Shiksha Online
Google Sheets and MS Excel are similar in terms of usage and formulas. While most of the features of these most popular spreadsheets are the same, there are some differences...read more
How To Remove Duplicates In Excel
How To Remove Duplicates In Excel
While working on spreadsheets, you may come across instances where you will see duplicate values. This may arise for different reasons, like multiple people working on the same document, unification...read more
Top MS Excel Interview Questions
Top MS Excel Interview Questions
Microsoft Excel is an indispensable tool that empowers professionals to manage, analyze, and visualize data with unparalleled efficiency. Master your Excel interview with confidence using our curated list of top...read more

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.”

How To Create A Pivot Table In Excel?
How To Create A Pivot Table In Excel?
A pivot table is a powerful tool in Excel that allows you to summarize and analyze large amounts of data. It is essentially a way of reorganizing and manipulating data...read more
How to Use HLOOKUP in Excel
How to Use HLOOKUP in Excel
The HLOOKUP in Excel enables finding data in a dataset horizontally. HLOOKUP stands for Horizontal and searches for a value in the top row of a table. The function then...read more
Tutorial – VLOOKUP in Excel – Shiksha Online
Tutorial – VLOOKUP in Excel – Shiksha Online
VLOOKUP, or vertical lookup, is one of Excel’s most commonly used functions for data analysis. This function belongs to the Search and Reference group, which has other search functions in...read more

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

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments