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

Can you provide a sample file please? And a screenshot of your text to columns wizard screens.

Answer 2

Hi,

I cannot reproduce this issue, is it possible to upload some sample data to your onedrive and post the link here?

Remove all confidential data!

Answer 3

Here is a screenshot.

Sorry, this is a problem with a TAB delimited CSV file.

Here is a link to the sample file.

Thanks!

Answer 4

Hi,

Because your list separator is a comma, Excel interprets alreay the data at opening even before you can use the split to columns.

Your original data (opened with a text editor)

"ID"    "Name"    "CountryID"    "URL"

271    "Texaf, S.A."    22    "www.texaf.be"

386    "Cochrane Design Group, Inc."    40    "www.cochrane-group.ca"

426    "Kaneff Corporation"    40    "www.kaneff.com"

2242    "Oriental University City Holdings"    46    "www.oriental-university-city.com"

Opened with Excel:

ID "Name" "CountryID" "URL"
271 "Texaf	S.A." "22" "www.texaf.be"
386 "Cochrane Design Group	Inc." "40" "www.cochrane-group.ca"
426 "Kaneff Corporation" "40" "www.kaneff.com"
2242 "Oriental University City Holdings" "46" "www.oriental-university-city.com"

Ans as it overwrites the cells to the right, you are loosing that part.

One solution is to change the extension to .txt.

Another solution is to use my add-in JP's Extension

It has a button to open csv files like this one.

Answer 5

Don't open the file using file, open. Instead, use Data, "From Text" and then point at the file.

See: www.jkp-ads.com/articles/importtext.asp