Different applications generate data in different forms.
A SaaS application might generate a report in the form of a PDF, while another application might generate data in the form of a TXT or CSV file.
The challenge lies in consolidating the data into a single application that can work with all the data to produce the required results.
Microsoft Excel is one application that is frequently used for this, owing to its versatility in importing data from files with a wide variety of extensions.
For example, if you have a list of items saved in a Text file (or in Notepad), Excel allows you to import this list so that you can process it further.
In this tutorial we will show you two ways to convert a text file to Excel:
Table of Contents
A Text file is one that contains text with no special formatting (as in bold, italic, images, etc.).
These files are identified with the ‘.txt’ extension and can be opened in any text editor, like Notepad or word processor, like Microsoft Word.
But in many cases, you can have data in a text file that you need to open in Excel and analyze there.
Thankfully, you can easily open text files in Microsoft Excel and then further work on it.
Let’s see how to do that!
Let us look at two ways in which we can convert a Text file (.txt) to Excel.
If you have a spreadsheet open in Excel and want to import the contents of a Text file at a specific area of your spreadsheet, the Text Import Wizard might be the best way to go.
This wizard scans your text file and ensures that the data in the file is imported the way you want.
To use the Text Import Wizard, select the Data tab, followed by ‘From Text’ (which you will find under the ‘Get External Data’ group.
After this, select the text file that you want to import.
Your Text Import Wizard should now open automatically.
The Text Import Wizard guides you in providing the best settings to import your text file data through 3 steps:
In this step you can provide the settings for the following:
If you had selected the ‘Delimited’ radio button in Step 1, then this step lets you specify the delimiters that your data contains (for example tabs, commas, semicolons, etc.) You can also specify if you want consecutive delimiters to be counted as one.
Moreover, if your text values are enclosed in a special set of characters (for example in single or double quotes), then you can specify the character in this step.
These characters are known as ‘Text qualifiers’ and help specify if you want a set of values to be imported as one.
For example, “Scott, Steve” is imported as a single value, even though the words are separated by a comma delimiter.
At the bottom of the screen, you can see a preview of how your data will get affected in the spreadsheet after conversion.
If you had selected the ‘Fixed Width’ radio button in Step 1, on the other hand, then Step 2 lets you specify widths for each column.
You can introduce a new column break in the preview window by simply clicking at the appropriate point on the scale.
A line with an arrow should appear to represent the column break.
Place line breaks wherever you need to in the preview window.
If you want to move a line break, simply drag it across. If you want to remove a line, simply double-click on it.
This lets you specify the data format for each column.
You can specify if you want a column to remain in the default format set by the Text Import Wizard or if you want to set the format to be General, Text, or Date.
Simply select a column from the preview window and select the format you want.
If you want to remove a selected column, select the last radio button option (which says ‘Do not import column‘).
The Advanced button in this step opens the Advanced Text Import Settings dialog box.
This dialog box lets you specify advanced formatting options.
For example, you can use the Advanced Text Import Settings to specify what decimal or thousands separators have been used in the text file, so that they can be made to match your computer’s regional settings.
Once you’re done providing all the settings for your imported text data, click on the Finish button.
This will open the Import Data dialog box.
In this box, you can select where you want your text file’s data imported.
If you want to import it into the existing worksheet, you can select or type in a reference to the cell where you want Excel to start displaying the imported data.
If you want to open it in a new worksheet, however, then select the radio button next to ‘New Worksheet’.
Your text file should now get imported to your selected location in Excel, with the format settings that you had specified in the Text Import Wizard.
You can also choose to simply open the text file directly in Excel.
While opening, Excel recognizes a text file and automatically converts it into a format that can be displayed in a spreadsheet.
All you need to do is click on the File tab, select Open and then browse and select the text file that you want to open.
One issue that you are likely to face when trying to open a text file directly in Excel is trying to locate the file in the folder.
This is because Excel’s ‘Open File’ dialog box, by default, filters, and displays only Excel files like those with extensions ‘.xls’, ‘.xlsx’, etc.
It also displays CSV files, if your computer is set up to use Excel as the default application to open CSV files.
So if you don’t see your required text file in the folder when trying to open it in Excel, simply select ‘All Files’ from the ‘File Types’ drop-down, as shown below:
This will display the Text Import Wizard from where you can set up how you want your Text file’s data to be displayed in Excel (as shown in method 1).
You should now see your text file converted and displayed in Excel.
Once the file opens, if you still find some of the columns (or individual cells) not converted to your required format, you can convert them manually.
While this method to open the Text file directly in Excel works well, the benefit of using the first method (using Text Import Wizard) allows you to make some adjustments even before getting the data in Excel.
In this tutorial, we showed you two ways to convert and open a Text file in Excel.
A third method could be by using online tools like Online2pdf, but we did not dwell too much on this because Excel’s Text Import Wizard provides much more flexibility, allowing you to adjust your data, and format them exactly how you want them.
We hope this tutorial was helpful for you.
Other Excel tutorials you may also like:
I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.
