How To Convert Delimited Text Files To Excel Spreadsheets

Ads by Google

excel logo   How To Convert Delimited Text Files To Excel SpreadsheetsI 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.

Ads by Google


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.

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. 012   How To Convert Delimited Text Files To Excel Spreadsheets

  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. 023   How To Convert Delimited Text Files To Excel Spreadsheets

  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. Step01   How To Convert Delimited Text Files To Excel Spreadsheets

    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. Step02   How To Convert Delimited Text Files To Excel Spreadsheets

  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. Step03   How To Convert Delimited Text Files To Excel Spreadsheets

  12. Click on Finish to exit the wizard and get your neatly arranged spreadsheet.
  13. Final   How To Convert Delimited Text Files To Excel Spreadsheets

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.

Ads by Google

3 Comments - Write a Comment

Reply

Chris

If the file is comma delimited, all one needs to do is change the suffix to .csv and then excel or just about any decent spreadsheet will open it automatically.

Reply

FunDreamz

@Chris
You have suggested the best and the shortest route. Rename file to .csv, open with excel and then save as a normal excel file. Your original .csv file remains intact for future use, if required and you have brand-new spreadsheet with data already converted.

Reply

Stu

Saikat,

Periodically, I have to open several delimited .txt files in Excel. They all have the same columns. Some of the columns contain codes with leading zeroes. If I leave the column data type as the default, General, the leading zeroes are dropped.

I have to go through your step 6 when opening each file and set the data type to Text.

Is there a template or something in which I can pre-define each column’s data type and avoid all that manual effort?

Your comment