How to convert text files to Excel (TXT to XLSX)
If you use text files to store and manage data, you've probably faced the challenge of converting these files into CSV, XLSX, or other Excel-compatible formats.
Excel is a powerful tool for automation, data analysis, modeling, reporting, and more. It can even handle large amounts of data efficiently.
However, you must first bring your data into Excel to make the most of these capabilities. Copying and pasting the data manually from a TXT file to Excel is not feasible.
The data might end up pasted in a single column or the wrong cells, and it can be time-consuming, especially with larger files. Plus, you risk losing or misplacing important information.
This article will cover how to convert text to Excel using online and offline tools and methods.
Say goodbye to the hassle of manually copy-pasting text files to Excel.
Securely capture, categorize, and extract data from unstructured documents into structured Excel files within seconds. Save time, reduce errors, and boost your productivity with Nanonets.
1. Open the text file using Excel
Import text to Excel by opening the text file directly in Excel.
This is another easy method to convert text to Excel. No additional software or online tools are required. Just follow the steps below:
- Open Excel and select the 'File' option from the menu
- Click on 'Open' and navigate to the location of your file
- Select ‘All Files (*.*)’ from the drop-down menu in the lower right corner
- Locate and select your text file, then click 'Open' (Note that Excel can handle various text file formats, including .txt, .csv (comma-separated values), and .tsv (tab-separated values))
- Excel's Text Import Wizard will appear — specify how Excel should interpret the data in your text file
- Choose the appropriate Delimiter (comma, tab, or other) that separates your data
- Preview your data and adjust any settings as needed, then click 'Finish'. (Use Excel's formatting options to correct data formatting errors with dates or numbers)
- Your text data will now appear in an Excel spreadsheet
This method is quick and straightforward, but it may not work as effectively if your text file is not correctly formatted or if the data consists of numbers, text, and dates.
Process thousands of TXT files in minutes.
Bulk extract text and data from thousands of structured and unstructured documents — while maintaining over 95% accuracy! Stop wasting time copying data between documents and spreadsheets. Automate the busywork so you can focus on what really matters.
2. Use Excel’s 'Get Data' Feature
Excel’s 'Get Data' feature is more efficient for importing and converting text to Excel. This feature supports a variety of data sources, including text files.
Here are the steps to follow:
- Open Excel and select the 'Data' tab from the menu bar
- Click on the 'Get Data' option and navigate to 'From Text/CSV'
- Locate and select your text file, then click 'Import'
- Excel will preview your data — choose the appropriate delimiter, file origin, and data type detection setting, then click 'Load'
- Get the text data in an Excel window, ready for further analysis and reporting
The ‘Get Data’ feature allows you to import data and gives you control over how Excel interprets the data. This can be particularly useful when dealing with large text files or complex data.
It is important to note that the 'Get Data' feature is only available in more recent versions of Excel (2016 and later). If you're using an older version, you may need to upgrade or try a different method.
Complicated document layouts hindering your progress?
Excel's 'Get Data' feature is handy but struggles with complex documents. Nanonets' intelligent OCR accurately converts challenging files into structured Excel data in real time. With advanced AI capabilities, you can automate data extraction and streamline your workflows. See Nanonets in action—schedule a demo now!
3. Copy-paste the tables from Notepad to Excel
One simple way to convert text to Excel is to copy the data from a text editor like Notepad and paste it into an Excel worksheet. Excel's Text Import Wizard lets you easily copy and paste tabular data from text files while maintaining the structure.
To copy a text table from Notepad to Excel using the Text Import Wizard:
- Open the text file in Notepad or any other text editor
- Select and copy the tabular data you want to convert
- Keep a note of how the values are separated (comma, tab, space, etc.) — the delimiter used
- Open a new Excel workbook and select the cell where you want to paste the table
- Paste and Select the Contents. Once selected go to Data -> Text to Columns.
- The Text to Columns dialog box will open — select 'Delimited' and click 'Next’
- Select the delimiter used in your text file (comma, tab, space, etc.) and click 'Next’
- Specify the data format for each column or use the default 'General' format
- Click 'Finish’, and the copied data will appear in the Excel worksheet, maintaining the original tabular structure.
This method works well for small tables but can be cumbersome for larger datasets. The data in the text file should be cleanly formatted, with proper delimiters and no missing or extra values.
Securely process your documents with a GDPR-Compliant OCR.
Online converters offer convenience, but Nanonets takes it a step further by prioritizing data security with GDPR-compliant OCR technology. It ensures the highest security standards and strict privacy policies for handling sensitive data during extraction and processing.
The next steps after importing text files into Excel
Converting TXT files to Excel is just the first step in the data analysis process. Once your data is in Excel, you can make use of Excel’s advanced features to manipulate and analyze the data.
Here are some of the things you can do:
- Text to Columns: This feature allows you to split a single column of data into multiple columns based on a specified delimiter. It's particularly useful when your original text file has data combined in a single field that you want to separate. It can be found in the ‘Data’ tab.
- TRIM, CLEAN, and SUBSTITUTE functions: These functions help you clean up and standardize your text data by removing extra spaces and non-printable characters or replacing specific characters or strings.
- Filtering and sorting: Typically, when you convert your data from a text file, it may not be in the ideal order for analysis. Excel's filtering and sorting features allow you to arrange your data based on specific criteria, making it easier to identify trends and patterns.
- Removing duplicates: If your text file contains duplicate entries, Excel can easily identify and remove them, giving you a cleaned-up dataset to work with. The "Remove Duplicates" feature is available in the ‘Data’ tab.
- Quick Analysis: Text tables may not be visually appealing when imported into Excel. Excel's 'Quick Analysis' tool, accessible by selecting the table data, provides instant access to various formatting, charting, and analytical features. This can help you quickly spot trends, patterns, and outliers in your data.
Final thoughts
Remember, getting your data into Excel is just the first step. With tools like Nanonets, you can automate the next steps in your data processing workflow, saving time, improving accuracy, and enhancing your overall productivity.
Whether you're an IT admin, a business owner, or a data analyst, having the right tools and techniques at your disposal can make a significant difference. More than getting your hands on the data, the focus should be on managing, processing, and using that data to drive insights and decisions.
Scale your document data extraction now!
Find out how Nanonets can be customized to your specific document types and data requirements. Schedule a demo to see how you can train custom OCR models and set up workflows that fit your business perfectly.
