I 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.
Let’s start with a text file with some data separated by commas.
To bring the data into Excel ““
- Open Excel and copy-paste the text contents into a spreadsheet. The contents take up one column and several rows.
- 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.
- Clicking on the above command opens the Convert Text to Columns Wizard.
- 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.
- 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.
- 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.
- Click on Finish to exit the wizard and get your neatly arranged spreadsheet.
In the Wizard, choose Delimited and click on Next.
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.