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

Aha, the file has no ; delimiters at all, which fooled me into believing it does not work.

If you use:

Sep=TAB

then it opens correctly in Excel.

Nice trick, but I like Data, "From Text" a lot more.

Answer 2

Jan karel,

Indeed, we can open the file with our Dutch/Belgian settings without problem, I got the issue after switching to International settings (option in my add-in). It all depends on the locals but also on the use of textqualifier and the sample file is not consequent in the use of it. Furthermore Excel interprets the extension and applies it.

I tried it with "\t" which didn't work. It must be my Solaris/Linux background to do think that this is applicable overall and anywhere, I forgot to think about MS.

Your solution is far away the best but it requires at lot of clicks. And that is the reason why I build this utility in my add-in.

A second thought, how to force the "supplier" of the csv to add the separator?

Answer 3

I changed the computer region from NZ to US and the problem went away.