We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How can I delete rows that don't contain specific words

asked 2018-06-06 00:32:17 +0200

JLemunyon gravatar image

I need to delete rows in a spreadsheet that do not contain certain words.

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2018-06-06 12:46:18 +0200

Grantler gravatar image

Only Calc

  • Search each cell containing your specific word(s) by SEARCH/REPLACE.
  • After cells have been marked hide them (menu Format > Row)
  • Delete rows containing other words but not the searched one
  • Show rows again
edit flag offensive delete link more

answered 2018-06-07 07:22:39 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.


Append or prepend a column. In that column, create a function that searches all other cells of the row for matches. Sort your sheet based on the results. Remove the "now gathered together rows" all-together.

If you want to keep the original sort, before sorting, create another column and put in it the original sort (unless you have other way to re-sort them properly). After the "elimination" of the unwanted rows, re-sort based on that column.

You can now remove the added columns.

edit flag offensive delete link more

answered 2018-06-07 09:04:43 +0200

pierre-yves samyn gravatar image


See an example of formula. In this example the data range is named "foo" and the list of words is named "words". The result of the formula is zero for lines for which no word is found

HTH - Regards

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-06-06 00:32:17 +0200

Seen: 2,510 times

Last updated: Jun 07 '18