Different applications generate data in different forms.
One SaaS application can generate a report in PDF format, while another application can generate data in the form of a TXT or CSV file.
The challenge is to consolidate the data into one application that can work with all the data and produce the desired result.
Microsoft Excel is a frequently used application due to its ability to import data from files with different extensions.
For example, if you have a list of items stored in a text file (or in Notepad), Excel allows you to import that list so that you can further process it.
In this tutorial, we’ll show you two ways to convert a text file to Excel:
- By using the Text Import Wizard
- By opening the file directly in Excel
What is a Text File?
A text file because it contains text without any special format (such as bold, italics, images, etc.).
These files are identified by the “.txt” extension and can be accessed using any word processor, such as Notepad, or a word processor, such as Microsoft Word.
But in many cases, you can have the data in a text file that you need to open in Excel and analyze there.
Fortunately, you can easily open text files in Microsoft Excel and then work with them.
We’ll see how you do it!
How to Convert a Text File to Excel?
Let’s look at two ways we can convert a text file (.txt) to Excel.
Method 1: Using the Text Import Wizard to Convert a Text File to Excel
If you have an Excel spreadsheet open and want to import the contents of a text file into a specific area of the spreadsheet, the Import Text Wizard may be the best way.
This wizard scans your text file and ensures that the data in the file is imported as you wish.
To use the Import Text Wizard, select the Data tab and then the “From text” item (found in the “Extract external data” group).
After this, select the text file that you want to import.
The Import Text Wizard should now open automatically.
The Import Text Wizard guides you through the most advanced settings for importing text file data in 3 steps:
Step 1 of 3 –
- Original data type: Here you can choose how you want to divide the data into columns. You have two options:
- Delimited: Select this option if the text file data is separated by tabs, colons, semicolons, or other characters.
- Fixed width: Select this option if all items in each column of your data have the same width.
- Start import at row: Enter or select the first row of data from which you want to start the import.
- File origin: Select the character set used in your text file, such as 437 (for the US OEM character set). Normally, it’s best to leave it at the default settings.
- My data has headers: Hold down this field to verify that the dataset in your text file contains a header.
Step 2 of 3 –
If you selected the “Delimiter” radio button in step 1, you can specify the delimiters available in your data (such as tabs, commas, semicolons, etc.) in this step.
Even if your text values are included in a special character set (such as single or double quotes), you can enter a character in this step.
These characters are known as “text qualifications” and can help determine if you want to import a set of values as one.
For example, “Scott, Steve” is imported as a value, even if the words are separated by a comma.
At the bottom of the screen you will see an example of how the data in the table will be affected after the conversion.
If you selected the ‘Fixed Width’ radio button in step 1, on the other hand, step 2 will allow you to enter the width of each column.
You can enter a new column end by simply clicking on the appropriate point on the scale in the preview window.
There should be a visible line with an arrow representing the break column.
In the preview window, place the line breaks where you need them.
To move the end of a line, just drag it. If you want to delete a row, just double-click on it.
Step 3 of 3 –
Lets you specify the data format for each column.
You can specify whether you want the column to remain in the default format set by the Text Import Wizard, or whether you want to set the format to General, Text, or Date.
Just select a column from the preview window and choose the desired format.
If you want to delete the selected column, select the last option on the radio button (which says “Do not import column”).
The Advanced button in this step opens the Advanced Text Import Settings dialog box.
For example, you can use Advanced Text Import Settings to determine which decimal places or thousands of punctuation characters are used in a text file to match the area settings on your computer.
When you have completed all the settings for the imported text data, click Finish.
This will open the Import Data dialog box.
In this field you can select where you want to import the data into a text file.
If you want to import it into the current worksheet, you can select or enter a link to the cell in which Excel should begin displaying the imported data.
To open it in a new sheet, select the radio button next to “New Sheet”.
Click the OK button.
Your text file should now be imported to the location of your choice in Excel using the formatting settings you set in the Text Import Wizard.
How to Subtract in Excel: Formula for Numbers, Percentages, Dates, and Times?
Method 2: Opening a Text File Directly in Excel
You can also choose to simply open the text file directly in Excel.
When opened, Excel recognizes the text file and automatically converts it to a format that can be displayed in a spreadsheet.
All you have to do is click on the File tab, select Open, and then browse to select the text file you want to open.
One problem you may encounter when trying to open a text file directly in Excel is to try to find the file in the folder.
This is because the Open Excel File dialog box displays filters and only Excel files, such as ‘.xls’, ‘.xlsx’, and so on, by default.
It also displays CSV files if your computer is set to use Excel as the default application for opening CSV files.
Therefore, if you can’t find the text file you want in the folder when you try to open it in Excel, just select “All Files” from the “File Types” drop-down list, as shown below:
The Import Text Wizard appears, allowing you to configure how you want the data in the text file to appear in Excel (as described in Method 1).
You should now see your text file converted and displayed in Excel.
If, after opening the file, you still find some columns (such as individual cells) that have not been converted to the desired format, you can convert them manually.
In this tutorial, we will show you two ways to edit and open a text file in Excel.
The third method may be to use online tools like Online2pdf, but we haven’t talked about that, because the Excel Text Import Wizard offers more flexibility, you can customize your data and format it exactly the way you want.
We hope this guide helps you.
Related Post –