6

I want to save an excel sheet as a csv file.
I choose 'Save as', select CSV file format and click on 'Save'.

When I open this saved file in textpad I see that excel used the ';' (semicolon) as delimiter for the cells.

I searched google for this problem and it was said that the list separator in the regions additional format settings should be changed.

I changed the systems list separator to ',' (comma), restarted excel tried to save it again, but still the semicolon is there. I restarted my computer, tried to save it again, but still the semicolon is there.

I searched all options of excel to set the list separator manually, but nothing there.

My questions:

  • How to set the list separator for excel 2010?
  • Why does excel ignores my changed list separator? (and how to repair it?)
Mixxiphoid
  • 1,155

4 Answers4

10

I finally found the answer.
It seems that the semicolon is a backup delimiter that excel uses if it can't use the comma for some reason.

That reason was in my case that the decimal symbol was already using the comma. Apparently it is not allowed to use the same character for the decimal symbol and the list separator.

To answer my questions directly:

  • How to set the list separator for excel 2010?
    You can set it in the Regional and Language settings.
  • Why does excel ignores my changed list separator? (and how to repair it?)
    It is possible that the decimal symbol uses the same symbol as the list separator, this is not allowed. See image below.

enter image description here

Prasanna
  • 4,174
Mixxiphoid
  • 1,155
1

The problem is that when writing a CSV file, Excel will use the local list separator but when closing the workbook it will overwrite the file with the default separator.

Solution to the problem is therefore:

Manually: Save As and then click NO when closing the workbook and excel asks to save the file

VBA do not use "Active.Workbook.Close True" but use "Active.Workbook.Close False"

this will force the local list separator to be used.

Rob Bronckers
  • 11
0

This answer seems to propose what you have already tried. Not sure why it did not work for you.

If you don't have any ; or , in the content of the cells, you could just open the file in Notepad and run a find&replace.

Alternatively, you can manually create the CSV file with some VBA code - see this and that for example. That gives you full control on the delimiter that you want to use.

Community
  • 1
assylias
  • 426
0

The final solution for those who do not want excel to split automatically the data of a csv file in more than one column (list separator like semicolon ; or coma, )when double clicking a csv file:

windows 7 control panel-> regional and language -->Format--> additional settings --> numbers --> list separator -->>> change it to what ever but coma or semicolon: f.e.: µ § somthing wird and unusual which probably anybody would write on the database.

then when double clicking a csv file all the data will remain in one column instead of listing it separatelly into another

chuwamba
  • 1