Excel 2013 text to columns - comma bug

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!

 

Question Info


Last updated February 24, 2020 Views 2,599 Applies to:

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Can you provide a sample file please? And a screenshot of your text to columns wizard screens.
Regards,
Jan Karel Pieterse
Excel MVP
https://jkp-ads.com
https://excelexperts.nl

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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!

Kind regards,

JP Ronse

If this response answers your question then please mark as Answer.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Here is a screenshot.

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

Here is a link to the sample file.

Thanks!

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

Kind regards,

JP Ronse

If this response answers your question then please mark as Answer.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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

Regards,
Jan Karel Pieterse
Excel MVP
https://jkp-ads.com
https://excelexperts.nl

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thank you very much for your response and solutions.

Hanan

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

Best regards
Vijay Verma

Timezone: UTC+05:30, PST+12:30
Availability Hours (UTC) : 05:00 AM - 05:00 PM

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi Vijay,

Interesting, I did not know that!

##EDIT##

I just tried and it does not work for me.

Regards,
Jan Karel Pieterse
Excel MVP
https://jkp-ads.com
https://excelexperts.nl

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Hi Jan Karel,

It works for me??? File: commas

Kind regards,

JP Ronse

If this response answers your question then please mark as Answer.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

I used below file to test the solution 

http://1drv.ms/1WlSahj

Best regards
Vijay Verma

Timezone: UTC+05:30, PST+12:30
Availability Hours (UTC) : 05:00 AM - 05:00 PM

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.