How To Convert Delimited Text Files To Excel Spreadsheets

Ads by Google

excel_logoI am not much of an Excel hand; spending most of my time either in Microsoft Word or PowerPoint. My most regular use of Excel is as a tool to keep track of my schedule and to list what I am putting out on the web as a writer. So I can safely say that at least in my case, the enormous power that’s in this spreadsheet program lies latent. But it happens to all of us that sometimes some tasks, if not taken, get thrust upon us (just like greatness!).

So it was with me when I had to convert a delimited text data file to Excel and format it within the columns and rows. That’s a rough challenge for a guy who has a chronic fear of anything that resembles figures. The raw file that I had in my hand consisted of higgledy-piggledy blocks of text and numbers. My simple task was to make sense out of this jumble by importing it into Excel and sorting the data into columns and rows.

Thankfully, many web tutorials came to the rescue of my dead brain cells.

Delimited files are a simple way to store data and import or export it between various applications. Delimited in essence means, data that’s separated by specific delimiter characters. Common delimiters are tab, comma and semicolon.

Think of delimited files as the most basic file format that’s almost uniformly accepted by most spreadsheet and database programs. For those of us who don’t go down that road, a common example is the CSV (Comma Separated Value) files or Tab-separated files we come across in the Import Export wizards of email programs like Outlook and Gmail.

A delimited file is of no actual use unless it is converted into some form suitable for analysis and study. Microsoft makes it easy to convert a delimited file to a spreadsheet in three easy steps.

Ads by Google

Let’s start with a text file with some data separated by commas.

To bring the data into Excel ““

  1. Open Excel and copy-paste the text contents into a spreadsheet. The contents take up one column and several rows.
  2. convert delimited file to excel

  3. Click on the column header to select the entire column. Click on the Data tab in the ribbon and then Text to Columns in the Data Tools group.
  4. convert delimited file to excel

  5. Clicking on the above command opens the Convert Text to Columns Wizard.
  6. Alternatively – You can directly import a text file into Excel. Excel handles file types – Text, Comma Separated Values and Printer Text File. Make sure to dropdown to All Files or Text Files in the File Open dialog while selecting the file to import. If Excel recognizes it to be a delimited file, it opens the Text Import Wizard which is similar to the Convert Text to Columns Wizard.
  7. convert delimited file to excel

    In the Wizard, choose Delimited and click on Next.

  8. In the second step, choose the Delimiter for you particular file (comma, in our case). For some other character, check Other and enter the character in the little field. The Data Preview window gives you an idea how the contents get separated into columnar data. Click on Next.
  9. convert delimited to excel

  10. In the final window, you get to set the data type for each column by selecting it in the preview. The default data format is General. Date and numeric values can also be handled here.
  11. convert delimited to excel

  12. Click on Finish to exit the wizard and get your neatly arranged spreadsheet.
  13. convert delimited to excel

It’s just three easy steps courtesy of a wizard. This simple utility gives us the ability to import delimited text files into Excel and apply its number crunching tools on it.

Liked this? We have other Excel tutorials to make an analyst out of you.

Join live MakeUseOf Groups on Grouvi App Join live Groups on Grouvi
655 Members
Windows Tips
Windows Tips
611 Members
Windows Troubleshooting
Windows Troubleshooting
576 Members
Best Windows Software
Best Windows Software
557 Members
Computer Hardware Talk
Computer Hardware Talk
505 Members
Affiliate Disclamer

This article may contain affiliate links, which pays us a small compensation if you do decide to make a purchase based on our recommendation. Our judgement is in no way biased, and our recommendations are always based on the merits of the items.

For more details, please read our disclosure.
New comment

Please login to avoid entering captcha

Log In