Ask Your Question

Is there a way to delete all the empty cells preceding data, basically left-justifying the cells into columns?

asked 2019-01-08 17:30:48 +0100

Havaneiss Dei gravatar image

I've begun a spreadsheet using data imported from Wikipedia (I know; in this case, it's to give me a convenient host of starting points for further research). The imported charts index successive cell values right, line by line, with merged cells appearing left. I've been selecting all, then un-merging the cells, then deleting the empty cells to re-align the data.

Presently, I have over 1200 lines of data (imported in chunks, so I haven't hit the AMJ column restriction). Still, shifting the cells left, one column at a time, is a pita. It would be nice if I could just highlight (select) a section, then click on something like "remove empty cells -> shift cells left" and let the software do the repetitive work for me.

Any ideas?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-01-13 06:40:25 +0100

libreofficeUser30872 gravatar image

Hard to visualize. No solution, just a couple of suggestions.

Is it possible to export the original wiki data to a text file, modify as necessary with your favorite script language to make it a nice csv file and then import to Calc.

On the Import window there is an option, "Merge Delimiters". From the help, "Merge delimiters: Combines consecutive delimiters and removes blank data fields."

When I select multiple cells and right click, a menu is displayed with options to "shift up, shift left, delete row, delete columns".

If the empty cells fill a column, just delete the entire column by right clicking a column letter.

The selected data can be pasted over the empty cells, so no deletion, just cut and paste.

If you used Data > More Filters > and set the filter Value to Empty for the appropriate column, all the rows will be displayed that match the filter (Empty). From there you might be able to select, cut and paste more rapidly.

Sometimes, when selecting an html table from your browser, you have to be very particular on what you grab or you'll get extra html formatting. It might be easier to type the headers or the last row/cell.

edit flag offensive delete link more


Wow. That is way over my head. I ended up deleting the cells manually.

An issue in the same spreadsheet involved oodles of tiny images of flags, each image being apparently hyperlinked to an article about the State that it represented; these objects seemed to cause my computer and the software to run very slowly, and occasionally made editing or using the spreadsheet almost impossible.

Using the shift key and manually clicking on each tiny jpeg to form groups, each of which I then deleted before repeating the process. It was slow going and I had to often manually save my work as I cleaned-up the sheet (fortunately, performance improved as the objects were deleted), but I am confident that I got either all, or nearly all, the offending objects.

Back to the instant matter, though: thanks for trying to help; I just wish I could give better feedback on ...(more)

Havaneiss Dei gravatar imageHavaneiss Dei ( 2019-01-14 20:54:36 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-08 17:30:48 +0100

Seen: 29 times

Last updated: Jan 13