Ask Your Question

Calc - Searching and Deleting Cells

asked 2017-09-15 12:03:29 +0200

Bedsheets gravatar image

updated 2017-09-20 12:37:47 +0200

I may be asking spreadsheet software to do things it's not supposed to so thought I'd check here.

I have huge spreadsheets that I have to cut down to size to make them manageable. All cells are just simple text. Firstly, I need to eliminate all cells that have a hyphen in them as this means there was an error in the test. Each sheet contains about 5000 cells and there are roughly 200 sheets per spreadsheet. I can search for ever instance of a hyphen '-' across sheets but is there any way to delete them all at once? Even if I could just highlight them all that would help a little.

Similarly I need to delete all cells with numbers in them as these indicate duplicates. A cell should contain QbdyuiUnvcpoIJYbhjk for example and I need to get rid of cells which look like Qbdyui86vcpoIJYbhjk since it contains a number and also any containing hyphens such as Qbdyu-Unv-poIJYbhjk

It may be that I need to use Libre's database software or something similar but any guidance would be much appreciated.

edit retag flag offensive close merge delete


Please tag your question correctly and rectify the subject. The question is not about the 'Math' component (for editing and rendering of mathematical formulas) but about 'Calc' (spreadsheets).

Lupp gravatar imageLupp ( 2017-09-15 12:53:28 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-09-15 12:47:26 +0200

Lupp gravatar image

updated 2017-09-15 12:50:03 +0200

You cannot delete cells.
You can delete rows. You can delete columns. You can delete cell contents.

In all these cases you first have to select the range to which the operation should be applied (if not to the complete sheet).
-1- Open the 'Find & Replace' dialog.
-2- Show 'Options'
-3- Enable 'Regular expressions'
-4- Enable 'Current selection only' (If wanted).
-5- Enter the character string .*(-|[0-9]).* into 'Find:'. (It's a regular expression.)
-6- Leave 'Replace:' empty.
-7- Click on 'Find All'.

The next step depends on what you actually want to delete.
Cell contents: Close the 'Search results' windowlet, press the 'Del' key.
Complete rows: Rightclick one of the highlighted cells, choose 'Delete...', choose 'Delete entire row(s)'.
Complete columns: Respecticvely

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-09-15 12:03:29 +0200

Seen: 1,556 times

Last updated: Sep 20 '17