Code Yarns ‍👨‍💻
Tech BlogPersonal Blog

Excel: Remove Columns in Delimiter-Separated Values File

📅 2011-Aug-17 ⬩ ✍️ Ashwin Nanjappa ⬩ 📚 Archive

 
 

A Delimiter-Separated Values (DSV) file is a text file with data in several rows, where the data elements in each row are delimited (or separated from each other) and the number of such elements in every row is the same. The most common example of such a file is a Comma Separated Values (CSV) file where the comma character is used as the delimiter. Other common delimiters are space, tab or any whitespace.

If you have access to Linux tools such as cut or awk, removing certain columns from a DSV file is easy! However, if you are using a computer with no such software, worry not since Microsoft Excel can deal with delimited text files easily. Here is how to remove columns from a DSV file:

1. Import DSV file into Excel

Open Microsoft Excel and choose to Open the delimited text file. Excel will display a Text Import Wizard, where you choose the file type as Delimited and in the following dialogs choose the kind of delimiter used in the file. The common delimiters are comma, tab or space. The delimited data from the file should now be displayed correctly in Excel, with one cell for each data element.

2. Remove the columns of data

To remove a column, right-click on its column name (at the top) and choose Delete.

3. Save the file

Save the file back using the Save As option. Here you will need to pick the delimiter format using which you want to save the file. Options include CSV, Tab delimited and Text.

Tried with: Microsoft Excel 2007