Calc : How to split (correctly) cells containing commas?

[Shane1]

Hi

I have a cell containing 3 consecutive commas – ,,,
I select the cell, then Data>Text to Columns>Separated by>Comma and the preview window shows 3 blank cells, as I would expect. However, when I click OK, the 3 consecutive commas remain in the original cell. Why are the contents not replaced with a blank?

Another strange thing – I have a cell containing the text

,2013-09-15 15:14:47,,

I select the cell, then Data>Text to Columns>Separated by>Comma and the preview window shows
[blank cell][2013-09-15 15:14:47][blank cell] as I would expect. When I click OK, why does the original cell remain unchanged?

My locale setting is English(UK), Decimal separator key is same as locale setting (.)

[oweng]

This would appear to be a bug under GNU/Linux running v4.1.0.4, or at least not consistent with the offered preview (as you indicate). Basically, given this data:

Column A
--------
a,b,c
,b,c
,,c
a,,
a,b,
a,,c

Highlighting the cells and selecting Data > Text to Columns… > checking Comma as the preferred separator on the Text Import dialog, results in this preview:

Yet, the result is:

A      B      C
-      -      -
a      b      c
,b,c   b      c
,,c           c
a		
a      b	
a             c

As can be seen, any situation with a leading delimiter is treated as a blank for overwriting purposes i.e., the original column A value is not overwritten. I can’t find an existing open (or resolved) bug relating to this issue so please raise a bug and report the bug number back here using the format “fdo#123456”. Feel free to include a link to this thread and my example if you feel it clearly explains the issue.

[Shane1]

Done. fdo#69981
https://bugs.freedesktop.org/show_bug.cgi?id=69981

[okierie]

ah, it’s nice. i didn’t think it would be easy

[Willsey]

I’ve found this quite an easy way when using a Mac - open the CSV file in Preview (as it’s a text file) and highlight all the data you want to use, then COPY it. Go to LibreOffice, open a new spreadsheet, then paste. Works perfectly every time. (and so does the above).

[MARLON1]

Yes, this is a very helpful, thanks so much, I’m finding this a year ago.