Ask Your Question

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

asked 2013-09-30 05:04:43 +0200

Shane gravatar image

updated 2015-08-29 21:28:23 +0200

Alex Kemp gravatar image


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 (.)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-10 06:57:10.497901

1 Answer

Sort by » oldest newest most voted

answered 2013-09-30 08:13:47 +0200

oweng gravatar image

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

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

Text Import options

Yet, the result is:

A      B      C
-      -      -
a      b      c
,b,c   b      c
,,c           c
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.

edit flag offensive delete link more



ah,, it's nice. i didn't think it would be easy :D

okierie gravatar imageokierie ( 2014-08-05 06:04:03 +0200 )edit

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).

Willsey gravatar imageWillsey ( 2016-03-21 06:54:12 +0200 )edit

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

MARLON gravatar imageMARLON ( 2017-10-25 05:48:35 +0200 )edit

Question Tools

1 follower


Asked: 2013-09-30 05:04:43 +0200

Seen: 53,040 times

Last updated: Sep 30 '13