How to Open Text File in Excel: Making Things Easy

Well, we do generally use Text files to store small amounts of data in order to keep our work in track. However, there are scenarios where you might need to get that data into and Excel spreadsheet. In could be your personal work preference, or a simple client demand.

If you’re in a situation like that and are wondering how to open Text file in Excel, you’ve come to the right place. Here, we’ll be discussing in detail the import and conversion process of a text file to an Excel worksheet.

No, it isn’t hassle free, but it’s not impossible either. So, if you’re willing to learn the methods, hop in, we’re in for a rather interesting ride.

How are Text Files and Excel Workbooks Different?

Well, since the topic concerns opening a Text file in Excel, we need to first recognize the fact that these two file formats are indeed different. Before we get into the conversion procedures, let’s take a look at how they differ from each other.

File Format

When it comes to file formats, Text and Excel files are different entirely. As you’ll see, text files are saved as .txt files. On the other hand, Excel files are generally saved as .xlsx files.

Usage

Normally, text files aren’t used to make lists of data or to keep records. Text files are more suited to keep in short notes or small reminders. It could be a list of phone numbers, names, or E-mail addresses.

Whereas, Excel files are used to store larger arrays of data that include numbers, formulas, calculations and so on. Excel is a professional software that can even track all sorts of records of large companies that have hundreds of employees,

Limitations

If we’re to talk about limitations, Text files are made to type in basic texts only. No formulas, bold or italic texts and no complex calculations. Text files are even not enough feature rich as Microsoft Word files.

And if we’re to talk about the limitations of Excel files, then they are simply not suited for writing in general. You can’t use it to type a paragraph. It’s mainly used to store all sorts of data. Be it numbers or something else.

How to Convert and Open Text file in Excel Worksheet

There really aren’t too many methods to explore when your goal is to convert and open a text file in Excel. We’ll be discussing three distinct procedures in this one. So, let’s begin without further delay.

Using Text Import Wizard

This process is most commonly used by general users. It might seem tricky at first, but it’s not that complicated to be honest. Just follow the steps below and you should be alright.

  • Open the Excel file, go to File on the ribbon and there, select Open, then go to Browse.

Using Text Import Wizard 1

  • Here, you have to select the Text file that you want to open in Excel.
  • In this tutorial, we’ll be working with this Text file in particular.

Using Text Import Wizard 2

  • Once the file is selected, you’ll have to select the right options in the Text Import Wizard window that pops up.
  • Depending on the data there is in the file, you have to select the options. If the data is separated with several characters like comma, space, semicolon, and so on, select Delimited. If they are aligned in columns with spaces, you’ll want to select Fixed width.
  • Now, if the first row of data is containing the headings, check the box on My data has headers.
  • Once that’s done, click Next.

Using Text Import Wizard 3

  • In the second step, you have to select the type of delimiters that are in the text file. In this case, we’ll be needing the tab and space delimiter. Also, you’ll have to select the Text Qualifier as none. Once that’s done, move to the next step.

Using Text Import Wizard 4

  • In the next step, select the General option as Excel can usually able to determine the data type automatically.

Using Text Import Wizard 5

  • After this, click Finish and you’re done.

Using Text Import Wizard 6

Using Get Data

This time, let’s talk about a method that’s a bit different from the procedure we discussed beforehand. This too isn’t all that hard to execute. Just follow the details laid down below to do it right.

  • The first thing you want to do is, go to the Data tab.
  • Here, under the Get and Transform data group, you’ll have to select From Text/CSV. This can be done by selecting the Get Data> From File> From Text/CSV option as well.

Using Get Data 1

  • Once you’ve selected it, the Import Data popup will appear and you’ll have to select the desired Text file from there.
  • Now, Excel will determine the delimiters automatically and the text will get divided according to the data sorted in the text file.
  • All you have to do now is, click Load.

Using Get Data 2

  • The spreadsheet should appear like this after that.

Using Get Data 3

  • In order to unlink the data that got imported from the selected TXT file, within the Ribbon, select Table Design > External Table Data > Unlink.
  • Then, again go to Table Design, then select Tools, and click on Convert to Range. This will make the Table Design and Query tabs disappear.
  • And voila! The conversion is complete.

Creating a Customized File Import Using Get Data

The procedure seen in the previous method is repeated partially here. Let’s talk about the way you can give it a different turn.

  • Once you get the Import Data option after going through Get Data under the data tab and selecting the Text/CSV option, instead of “Load” select “Transform Data”
  • Once you’ve done that, you’ll see the data of the Text filein a Power Query Editor. Now, go to Home > Split Column > By Delimiter.

Creating a Customized File Import Using Get Data 1

  • This time, you have control in selecting the delimiter instead of letting it all happen automatically. Depending on your data, you can select hyphens, spaces, and so on.
  • Then, check the Each occurrence of the delimiter option.

Creating a Customized File Import Using Get Data 2

  • Now, all you need to do is, select Close and Load once you see the data in your Text file get split into different columns.
  • All you need to do after that is, go through some cell formatting.

Wrapping Things Up

Well, I bet you’ve got a complete idea on the topic we’ve discussed by now. Personally, the last two procedures are faster and more automated than the first one. But if you prefer a more hands on approach, the first method is best suited for you.

But hey, the choice is yours. You may apply any of them you please according to your situation. Enough said. Let’s close this up for now. See you in the next blog.

Leave a Comment