Automate your workflow with Nanonets
Request a demo Get Started

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.

Text files Excel spreadsheet

Tired of manual data cleaning after converting text files to Excel?

With Nanonets, you can automate data extraction from text files, PDFs, and images. Accurately process complicated documents and export the structured data directly to Excel or your favorite tools. Say goodbye to data entry headaches now.

This article will cover how to convert text to Excel using online and offline tools and methods.

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

Copy text tables from Notepad to Excel using Text Import Wizard

To copy a text table from Notepad to Excel using the Text Import Wizard:

  1. Open the text file in Notepad or any other text editor
  2. Select and copy the tabular data you want to convert
  3. Keep a note of how the values are separated (comma, tab, space, etc.) — the delimiter used
  4. Open a new Excel workbook and select the cell where you want to paste the table
  5. Click on the Paste menu — in the 'Paste' menu, select 'Use Text Import Wizard’
  6. The Text Import Wizard dialog box will open — select 'Delimited' and click 'Next’
  7. Select the delimiter used in your text file (comma, tab, space, etc.) and click 'Next’
  8. Specify the data format for each column or use the default 'General' format
  9. Click 'Finish’, and the copied data will appear in the Excel worksheet, maintaining the original tabular structure.
Select the right delimiter (comma, tab, semicolon) to get correctly structured Excel tables
Select the right delimiter (comma, tab, semicolon) to get correctly structured Excel tables
💡
The delimiter is the character that separates your data fields. Common delimiters include commas (,), semicolons (;), and tabs. Choosing the correct delimiter is crucial to ensure your data is imported correctly into Excel.

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.

Say goodbye to the hassle of manually copy-pasting text files to Excel

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.

2. Open the text file using Excel

This is another easy method to convert text to Excel. No additional software or online tools are required. Just follow the steps below:

Opening text files with Excel
Opening text files with Excel
  1. Open Excel and select the 'File' option from the menu
  2. Click on 'Open' and navigate to the location of your file
  3. Select ‘All Files (*.*)’ from the drop-down menu in the lower right corner
  4. 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))
  5. Excel's Text Import Wizard will appear — specify how Excel should interpret the data in your text file
  6. Choose the appropriate Delimiter (comma, tab, or other) that separates your data
  7. 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)
  8. 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 PDFs in minutes!

Process thousands of documents 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.

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

Using Excel's Get Data Feature
Using Excel's Get Data Feature

Here are the steps to follow:

  1. Open Excel and select the 'Data' tab from the menu bar
  2. Click on the 'Get Data' option and navigate to 'From Text/CSV'
  3. Locate and select your text file, then click 'Import'
  4. Excel will preview your data — choose the appropriate delimiter, file origin, and data type detection setting, then click 'Load'
  5. 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.

Enhance complex document conversion with Nanonets' intelligent OCR!

Enhance complex document conversion with Nanonets' intelligent OCR!

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!

4. Convert Text to Excel with online convertors

Simple online converters are available that can streamline the process of converting text files to Excel. These tools can provide output in various Excel-compatible formats, such as CSV, XLS, and XLSX.

Some of the popular online converters include:

These tools are straightforward to use. Generally, you need to:

  1. Go to the converter's website
  2. Upload your text file
  3. Select the desired output format (e.g., CSV, XLS, or XLSX)
  4. Click 'Convert' or a similar command
  5. Download the converted file and open it in Excel for further use.

Remember that while online converters are convenient, they may not be the best option if you're dealing with sensitive or confidential data. Always ensure you use a secure and reputable service if you use an online converter.

Prioritize data security with Nanonets' GDPR-Compliant OCR!

Prioritize data security with Nanonets' 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.

5. Batch processing multiple text files using VBA

If you have multiple text files that you need to convert to Excel format, manually opening and importing each file can be time-consuming. Fortunately, Excel offers a way to automate this process using VBA (Visual Basic for Applications) scripts.

By creating a VBA macro, you can loop through all the text files in a specified folder and import the data from each file into a new worksheet within your active Excel workbook. This automation can significantly speed up your workflow when dealing with multiple text files.

To create a VBA macro for batch processing text files, you'll need to:

  1. Open the Visual Basic Editor in Excel (press Alt+F11)
  2. Create a new module and write a VBA script that does the following:
    • Specifies the folder path where your text files are located
    • Loops through each text file in the folder
    • Creates a new worksheet for each file and sets the worksheet name based on the file name
    • Imports the data from each text file into the corresponding worksheet
  1. Run the macro (Press Alt + F8) to import all the text files into your Excel workbook automatically

Remember, creating a VBA macro requires some programming knowledge. You'll need to be familiar with VBA syntax and concepts. If you're not into programming, there are other ways to batch-process text files, like Nanonets designed for this purpose. These tools usually have user-friendly interfaces and require minimal setup to automate the text-to-Excel conversion process.

Automate batch processing effortlessly.

Automate batch processing effortlessly.

Writing VBA scripts can be a real hassle. But with Nanonets, you don't need to be a coding whiz. Our AI-powered OCR lets you automate bulk text file processing with ease. Extract data from multiple files and export it to Excel with just a few clicks.

6. Automate data extraction and processing with Nanonets

Sometimes, processing data doesn’t stop at converting to Excel. What if you must enter the data in the converted file to another system or database? Or you need to extract specific data from the converted file for analysis? This is where Nanonets can help.

Nanonets is an AI-powered tool that swiftly and smartly extracts data from many sources: PNG, JPG, PDF, Text, and Excel — in bulk. It automates your data extraction process from start to finish, even when it comes to unstructured data.

Here's how Nanonets extracts tabular data from text files to Excel
Here's how Nanonets extracts tabular data from text files to Excel

By automating your data extraction and processing, your team can avoid tedious manual data entry, oversights, errors, and endless approval follow-ups. And Nanonets handles everything from text files to customer orders, invoices, receipts, claim forms, and more. The best part? You don't need to have any coding experience to set it up.

With built-in approval workflow, you can review and approve the extracted data before it's transferred to your systems. This ensures data accuracy and integrity.

Extract data from text files Export the data as spreadsheets to different tools

Seamless data flow is just a step away.

Connect with over 5,000 apps via Zapier, APIs, and webhooks and automatically route data extracted from text files to your business apps, eliminating manual data entry—no coding required.

Here are the steps to use Nanonets:

  1. Create a Nanonets account
  2. Select the 'Table OCR' model from the main dashboard
  3. Upload your file or connect your data source
  4. Select the data you want to extract
  5. Nanonets will automatically extract the data
  6. Review, approve, or edit the extracted data
  7. Download the data or send it to your existing systems with one click

Nanonets works well with Google Sheets, Zapier, Xero, Sage, QuickBooks, and more. You can send your data straight to your business systems and set up automated workflows for consistent and accurate data processing.

Using Nanonets for further data processing
Using Nanonets for further data processing

We offer pre-built models for specific tasks such as invoice processing or receipt extraction. These models tackle complex data extraction, freeing up your time and resources.

For the tech-savvy, Nanonets also provides API access, allowing developers to tailor the tool to their specific data extraction and processing needs.

The more you use Nanonets, the smarter it gets. The AI learns from your inputs and corrections, becoming more accurate with each use.

The benefits of using Nanonets include:

  • Cutting down on manual data entry time
  • Minimizing human error and enhancing data accuracy
  • Easily convert unstructured data into structured data
  • Make scanned documents searchable
  • Integrating directly with your usual systems and tools
  • Boosting productivity and efficiency
  • No need to set up templates
  • Multiple export options
  • GDPR compliance
Effortlessly extract pages from Word docs

Don't settle for manual data entry and slow processing!

Stop wasting hours on manual data entry. Nanonets' AI-powered platform accurately extracts data from any document format, saving you time and effort. Focus on what matters most while Nanonets handles the rest!

The next steps after importing text files into Excel

Converting text files and bringing them 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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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. 

Convert txt to xlsx _ Book a demo

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.


FAQs

Can you convert a txt file to Excel?

Yes, you can convert a .txt file to Excel using several methods:

  • Open the .txt file directly in Excel
  • Use Excel's Text Import Wizard under the Data tab
  • Copy the text and use Excel's Text to Columns feature
  • Try an online converter tool

How to convert text to columns in Excel?

To convert text to columns in Excel:

  • Copy the text into your spreadsheet
  • Select the data
  • Go to Data > Text to Columns
  • Choose Delimited or Fixed Width
  • Select the appropriate delimiter (comma, tab, space, etc.)
  • Click Finish to split the text into columns

If you need to convert text to columns for multiple files or regularly, consider using Nanonets. It can automate the process, handle complex formatting, and integrate with Excel for a seamless workflow.

How do I create a text file from Excel data?

To create a .txt file from Excel data:

  • Select the data in your spreadsheet
  • Go to File > Save As
  • Choose a location and enter a file name
  • Under 'Save as type', select 'Text (Tab delimited)'
  • Click Save

How do you convert text to value in Excel?

To convert text to values in Excel:

  • Select the text-formatted cells
  • Press Ctrl + C to copy
  • Right-click and select 'Paste Special'
  • Choose 'Values' and click OK

How do I import a text file into Google Sheets?

To import a .txt file into Google Sheets:

  • Open a new spreadsheet
  • Go to File > Import
  • Upload the .txt file or select it from Drive
  • Choose 'Replace spreadsheet' or 'Insert new sheet'
  • Set the Separator type (detect automatically or specify)
  • Click Import data

For bulk imports or recurring text to Google Sheets conversions, Nanonets can help. It uses AI to extract data from text files and can export the results directly to Google Sheets, saving you time and effort.

How do I convert a text file to Excel without delimiter? 

If your .txt file doesn't have delimiters:

  • Open a blank Excel spreadsheet
  • Go to Data > From Text/CSV
  • Select your .txt file and click Import
  • Choose 'Fixed Width' or 'Delimited' based on your data structure
  • Adjust the settings in the preview window if needed
  • Click Load to import the data

For more advanced text to Excel conversions and automated data extraction, consider using an AI-powered tool like Nanonets. It can handle bulk conversions, extract data from unstructured formats, and integrate with your existing systems to streamline data processing workflows.


What are the different types of text files?

When converting text files to Excel, you may encounter different file formats. Each format has its own characteristics and compatibility with Excel.

Here's a quick reference table to help you understand the differences between common text file formats:

File Format Characteristics Excel Compatibility
.txt - Plain text file
- Can be tab-delimited or space-delimited
- Can be opened directly in Excel
- May require manual formatting
.csv - Comma-separated values
- Each line represents a row of data
- Can be opened directly in Excel
- Excel automatically recognizes the comma delimiter
.tsv - Tab-separated values
- Each line represents a row of data
- Can be opened directly in Excel
- Excel automatically recognizes the tab delimiter

If manually converting text to Excel is draining your productivity, imagine what else could be optimized in your daily operations. Enter Nanonets' Workflow Automation, a game-changer for businesses looking to streamline tasks. With the ability to create workflows in minutes using simple natural language, you can automate data-heavy processes and ensure seamless integration with your existing apps. Elevate your efficiency by integrating AI to sort, analyze, and input data directly into Excel, all while maintaining accuracy with Human-in-Loop validations. Explore the future of workflow automation and say goodbye to manual monotony.

Learn More