The Comma and Semicolon Issue in Excel Due to Regional Settings when open CSV file

There are a problem with excel 2010 that happened to my customer.When opening standard CSV (Comma Separated Values) files in Excel 2010 you may find that Excel did not recognize the fields and simply displays all data in the first column.

The problem is:

  • The standard field delimiters for CSV files are commas “,”
  • On American Windows versions, the comma is set as default for the “List Separator”, which is okay for CSV files.
  • But on European Windows versions this character is reserved as the Decimal Symbol and the “List Separator” is set by default to the semicolon “;”

After search the problem in google, I found a way to solved it. The solution is to change regional settings.

Please follow below step to solved it  :

  • go to Start | Control Panel | Regional and Language Options
  • Click Additional Settings
  • For Decimal Symbol, enter a dot  “.”
  • For List Separator, enter a comma “,”

Now, when you open a CSV file in Excel it will automatically find the data fields and open it appropriately. 😀

References Link :

http://www.paessler.com/knowledgebase/en/topic/2293-i-have-trouble-opening-csv-files-with-microsoft-excel-is-there-a-quick-way-to-fix-this

5 comments

  1. trappedincandy · June 4, 2014

    A very similar problem to this was driving me crazy this morning. In my class students have MacBooks set up in French, Chinese, Korean and at least two versions of English. A student was trying to use an IF command, something like =IF(month(A2)=1,B2,””). It worked on my computer but not his, until we tried using semicolons instead of commas.

    Thanks for your article, as it is clear enough for me to “translate” your instructions into Apple and change the list separators default globally.

  2. Craig Nichol · March 11, 2016

    Thanks dude:)

  3. maxime veilleux · November 10, 2017

    Change the background color. My eyes are bleeding.

    • akbaraji · November 10, 2017

      haha..thanks for the suggestion… I already changed it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.