Pinterest Stumbleupon Whatsapp
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.

  1. EMMA
    December 1, 2016 at 4:18 pm


  2. Vanya
    September 20, 2016 at 8:50 pm

    Thanks alot, this helped much

  3. Jody
    June 19, 2016 at 3:10 pm

    Thank you so much. This really helped. I have a column with values which I think are just end tags. No worries since I won't be using them. Thank you very much!

  4. umesh
    February 18, 2016 at 7:00 am

    i have sheet contain address , when i am converting in excel and using comma then the address separated in diff me

    • Saikat Basu
      February 18, 2016 at 2:42 pm

      Depends on what you want to achieve Umesh. The "comma" is being taken as a column delimitator.

  5. Doucheman Macgee
    March 3, 2015 at 12:01 am

    You are a lifesaver. Thank you.

  6. Stu
    January 14, 2010 at 11:30 am


    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?

  7. FunDreamz
    November 9, 2009 at 7:59 am

    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.

  8. Chris
    November 8, 2009 at 4:16 pm

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *