How to properly open a .csv file in Excel 2010?

Most of the time, opening a .csv file in Excel 2010 does not pose any problems, but sometimes it looks something like in the screenshot here.

By default, the data fields in a .csv file are separated via a “,” (comma), but when they are separated by a “;” or a “|” or another character, it displays the contents within the A column of the spreadsheet.

There are two options:

1. Select the entire A column, go to Data->Text to columns->Choose the delimited option->Select your delimiter (“;”, or “|”, or “,”, etc.) and finalize the process. Most of theĀ  time this method works.

2. This other method will ensure that any .csv files with a specific delimiter opened in Excel 2010 will be displayed properly. You can change this at any time if you change your mind and you want to keep the default “,” delimiter.
In Windows 7 go to Control Panel->Region and Language->Additional Settings->List separator. In here just replace the “,” with your desired character, usually “|”. Click OK and you’re set.

Be advised that the second option might create further problems as it also changes how other problems read delimiters and it definitely changes how Excel functions are written. It is recommended you change it back to “,” after you’re done with the .csv file.

Leave a Reply

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

*