Your data could be in any form from PDF, TXT, PNG, JPG, to CSV files. Some applications create files in the form of a PDF whereas other apps generate data files in the form of a TXT or CSV file. On the whole, you must be struggling to convert a Text file to an Excel spreadsheet because switching data into a single file can work differently instead of multiple files.
With Microsoft Excel, you can do this task frequently because it has many features that can help you execute multiple functions. Suppose, you are having a list of items in a TXT file, with Excel you can easily import the list to process it differently.
Here you will get to know exactly how converting a text file to an Excel spreadsheet could be an easygoing thing.
You can do this by:
- Using the Text Import Wizard
- Opening the file directly in Excel
So, before getting into the boat let’s have a look at what a text file basically is?
What is a Text File?
In a text file, as the name shows, you will see the text without special formattings such as italic, bold, images, or other formats. A text file appears with a .txt extension and you can view this file in any text editor such as Notepad or a word processor like Microsoft Word. However, you can open a text file in Excel to make further changes.
Let’s get to know how it all happens:
Converting a Text File to Excel Spreadsheet – How it Works
Normally, two methods exist for this as mentioned earlier:
- Text Import Wizard
- Direct File open in Excel
Using the Text Import Wizard to Convert a Text File to Excel
The Text Import Wizard feature could be one of the best tools when you need to import the data of a text file at some specific place on the spreadsheet of Excel.
Your text file gets scanned using the Wizard tool and it ensures that the data in the file is imported exactly as you want it to.
To make this tool functional, simply choose the Data tab given in the “From Text” menu located under the “Get External Data” group.
Later on, choose the text file you need to import.
Now, you will see the Text Import Wizard appears automatically.
Here are 3 steps that will guide you further to import the text file:
Step 1:
At first, you may need to provide the settings for the following:
Original Data Type:
In this option, you have to choose how your data should be separated into columns. You have two options:
Delimited: If the text file data is being separated using tabs, colons, semicolons, or other characters, you will have to choose this option.
Fixed Width: If each item in the columns of your data has similar width then you should choose this option.
Start Import at Row:
Type or choose the first row of the data right from you want to start importing.
File Origin:
Choose the character set that your text files use such as 437. Generally, you can leave it in the default setting.
My Data has Headers:
If the data set of your Text file has headers, then you have to check this field.
Step 2:
In case, if you had selected the “Delimited” button in the earlier step, then it is the time to indicate the delimiters in your data such as commas, tabs, semicolons, etc. You can even specify the consecutive delimiters in the data to be as one.
Further in this step, you can indicate the character if the text values are covered in a special set of characters such as single or double quotes. Do you know these characters are known as “Text qualifiers” that can help in indicating the set of values to be counted as one? For instance, “Scott, Steve” is considered as a single value, whereas a comma delimiter separates both words.
Once the conversion is done, at the bottom of the screen a preview of how your data is affected in the sheet will appear. In this step, you can identify widths for all columns if you had selected the “Fixed Width” button in the earlier step. If you click at some useful pointers on the scale, you can bring in a new column break in the preview window.
The column break is represented with a line having an arrow. To move a line break, you will have to drag it and if you want to remove it, just double click on it.
Step 3:
Once you are done specifying the values, now you should specify the data format for all columns. It’s up to you whether you need the column to be in the default format that the Text Import Wizard has set for you or you may want to set the format to be General, Text, or Date.
To select the format you need, just select a column from the preview window. To remove a selected column, just choose the last radio button saying, “Do not import column”.
By pressing the “Advanced” button, a dialog box of the Advanced Text Import Settings will pop up.
From this dialog box, you will find options to specify advanced formatting. For instance, to specify what decimal or separators have been used in the text file, you may use the Advanced Text Import Settings. It is because they must be compatible with the regional settings of your computer.
Click on the Finish button once you are done with all the settings for the imported text data.
Clicking on the finish button will pop up the Import Data dialog box.
From this box, you can then choose where you need the data of your text file imported. You have to choose or type in a reference to the cell if you need to import this data into the current worksheet. On the other hand, if you need it to open in a new worksheet, then choose the radio button next to “New Worksheet.”
Now, click on the OK button and the file will start importing to the designated location in Excel.
Using the Direct File Open to Convert a Text or CSV File into Excel
In Excel 2007, 2010, 2013, and 2016, you will see all the steps are similar to importing a TXT or CSV file into Excel.
Go to the Excel spreadsheet that is needed to have the data and click on the Data tab.
Choose the “From Text” option given in the Get External Data group.
Choose the TXT or CSV file that is needed to convert and click on the Import option.
Now, choose “Delimited”. As you know a delimiter is just a new way that explains you TXT file has tabs or the CSV file has commas to separate and group the data.
Choose the Next option.
In the Data preview field, choose the delimiter that helps in grouping the data in a separate column. Basically, you have to choose “Tab” while converting a TXT file to Excel spreadsheet and “Comma” while converting a CSV file.
Also, note that selecting the “Space” option could split a single column into multiple columns at times when the header has more than one word.
Now, click on the Next option.
Choose “General”.
Click on the “Finish” button.
Select “Existing Worksheet” and add “=$A$1” to the field. It will make sure that data starts in Row 1, Column A of the spreadsheet. Having a blank row in the sheet could lead to a file import error.
Click on the OK button.
Click Save.
