Ask Your Question

Condense data by deleting empty cells and moving full cells to the left

asked 2019-07-30 17:03:21 +0100

nke gravatar image

Hi all,

I have a rather messy dataset, seen in the attached screenshot: image description

Is there a way to delete all the empty cells and move the full cells to the left without overwriting any data to achieve the following result: image description

Thank you for your help! Best,

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-08-11 21:08:35 +0100

MrZoolook gravatar image

updated 2019-08-11 21:21:03 +0100

If you were to save the spreadsheet as a 'Text CSV' file, you can then open it back up in Libre Calc, and should be presented with a dialogue box similar to this:

image description

Here, you can select which options best suit your needs. Judging by your example, I'd suspect you can probably accept the defaults, but of particular help to you are the Separator Options. Note the 'Merge delimiters' tick box. A delimiter is what Libre will interpret as a new cell marker during importing. Usually, in a CSV file, they will be commas, as CSV stands for Comma Separated Values (or variables). Merging the delimiters will effectively force multiple empty cells to be skipped. Play around and note the preview in the lower portion of the box. You may not get the exact look you're after (particularly if you have cells with commas or spaces in them etc), but with some playing around, it should all but get things perfect. In my example, my column and row titles had spaces, so I had to untick 'Space' as well.

image description

It's hard to see your exact needs from your example, but you may get problems if you NEED empty cells. If so, you could temporarily put some known unique string in the cells you need to stay empty. Then after exporting as the 'Text CSV' file, and opening back up in Calc again, you can do a search and replace on that string.

To further help if your cells contain commas, spaces, etc... when you save as 'Text CSV', you could even specify which delimiter you wish to use. or define your own for good measure.

image description

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-07-30 17:03:21 +0100

Seen: 1,243 times

Last updated: Aug 11 '19