Libre Office – Formula to exclude cell

Method 1: Using AutoFilter (Recommended)

This method allows you to instantly display only the rows that do not contain your text, which can then be deleted. 

1. Enable Filter: Select your header row and go to Data > AutoFilter (or press Ctrl+Shift+L).
2. Filter for Text: Click the dropdown arrow in the column header that contains the text you are checking.
3. Use Standard Filter: Select Standard Filter….
4. Set Condition:
   • Field Name: Select the column.
   • Condition: Choose Does not contain.
   • Value: Type the specific text you want to search for.
5. Delete Rows: Select all the visible rows, right-click on the row numbers, and choose Delete Rows.
6. Remove Filter: Go to Data > Remove Filter to show the remaining data. 

Method 2: Formula to Flag Rows

If you prefer a formula approach to create a “helper” column, you can use IF with ISNUMBER and SEARCH (case-insensitive) to mark rows. 

Assuming your data is in column A and you want to keep rows containing “Yes”:

1. In a new column (e.g., B1), enter this formula:
   =IF(ISNUMBER(SEARCH("Yes"; A1)); "Keep"; "Delete")
   (Note: Use a comma , instead of a semicolon ; if your locale settings require it).
2. Fill this formula down for all rows.
3. Filter the helper column (B) to show only “Delete”.
4. Delete those rows. 

Method 3: “Find and Replace” (Alternative)

1. Press Ctrl+F to open the Find and Replace dialog.
2. Type the text you want to keep.
3. Click Find All.
4. Invert the selection: Choose Edit > Select > Select All (this may not work in all versions, making Method 1 or 2 better), or simply use the Filter method (Method 1) as it is more reliable for inverting selections.

Wildcards: You can use * as a wildcard (e.g., *text*) in the Standard Filter. 

Case Sensitivity: SEARCH is not case-sensitive; use FIND for case-sensitive searches.

Deleting Hidden Rows: After filtering, ensure you are only selecting and deleting visible rows.