Excel 2013 text to columns - comma bug

Question

Summary of the solutions to this problem:

Excel insists that a CSV file is "comma separated" even though you tell it otherwise.

In order to overcome this bug, change the file extension to .txt or open the file using data > from text.

Microsoft, please fix this bug. Thanks.

Hi,

I am using the text to columns feature but there seems to be a bug.

I export a CSV file from another application with the delimiter set to SEMICOLON and enclose strings with "

Open the file in Excel, and use the text to columns feature.

I set the delimiter to SEMICOLON only.

When I click text to columns I get the data in columns but whatever data in a cell that has comma disappears.

Example source:

"Stone company, Copenhagen";"Denmark"

Example result:

Stone company	Denmark

I have double checked many times that the comma delimiter checkbox is not set.

Tried to set and reset the comma delimiter checkbox before converting.

Looked in the settings to see if there is some setting I can change but didn't find it.

Nothing helps.

I am now removing the commas in a text editor before importing in order to avoid the problem but this is annoying.

Any idea how to fix this other than wait for the next version?

Thanks!

Answer 1

Thank you very much for your response and solutions.

Hanan

Answer 2

Another solution is to if you want to double click and open csv file directly - 

Add below as the first line of your csv file.

sep=; 

Now, Excel will treat semi colon as the separator and will parse your csv file accordingly. Excel will disregard the first line and will show the data from 2nd line onward only. First line, Excel will treat as instruction.

Answer 3

Hi Vijay,

Interesting, I did not know that!

##EDIT##

I just tried and it does not work for me.

Answer 4

Hi Jan Karel,

It works for me??? File: commas

Answer 5

I used below file to test the solution 

http://1drv.ms/1WlSahj