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

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

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


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.


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