The “Delete cells by content” procedures in the !SEMTools add-in clear or delete the cells that match given criteria in a few seconds. It’s handy for cleaning data, removing unwanted values or preparing tables for further processing. First, though, let’s look at what Excel can do on its own.
Built-in ways to delete cells by content
Find All
- Select the range and press Ctrl+F.
- Type the text to look for and click Find All.
- Press Ctrl+A in the results list: all found cells get selected on the sheet.
- Close the dialog and press Delete to clear them, or right-click → Delete to delete the cells with a shift.
Find All supports wildcards: “*@*” finds all cells with an e-mail, “sale*” all cells beginning with “sale”. Remember that the asterisk itself has to be escaped with a tilde.
Filter
Turn on the filter (Ctrl+Shift+L), choose Text Filters → Contains, Begins With or Ends With, select the visible rows and delete them. This deletes whole rows, not individual cells.
Go To Special for blank cells
To delete empty cells, select the range, press F5 → Special → Blanks → OK, then right-click → Delete → Shift cells up. To delete whole empty rows, see how to delete empty rows in Excel.
A formula in Excel 365
In Excel 365, FILTER returns a copy of the list without the unwanted values, for example without the cells containing “test”:
=FILTER(A2:A100,NOT(ISNUMBER(SEARCH("test",A2:A100))))
Each of these ways works, but takes several steps: a dialog, a filter, a helper column, then deleting the original. Hence the add-in procedures.
How to use the !SEMTools procedures
The typical scenario is simple: select the range, run the procedure from the menu, set the criterion (text or a list of values), and !SEMTools clears the contents of the matching cells. If the criterion is clear from the name of the procedure, the deletion happens right away, without extra questions.
The path to the tools is as obvious as can be: Delete → Cells → By content. This section has several groups of commands.
Delete empty cells
Empty cells that contain nothing also count as cells containing something. After all, they contain emptiness :) The Empty cells submenu removes all empty cells in the selected range and shifts the remaining data:
- Shift up removes the empty cells and moves the data up.
- Shift left removes the empty cells and moves the data from right to left.
Delete cells containing any value from a list
The Containing any from list group is useful when you have a list of values to delete. The list can be a range on the sheet or typed manually for single values.
- Anywhere deletes the cells where any value from the list occurs in any part of the text.
- At the beginning deletes only the cells where a value from the list is at the start of the text.
- At the end deletes the cells where a value from the list is at the end of the text.
Delete cells by words
The Containing words group finds and deletes cells by whole words. Unlike “containing”, it won’t delete “cart” when you look for “car”:
- 1 word deletes the cells containing the word you type in the text box.
- Any from list deletes the cells containing at least one word from the specified range.
Delete by regular expression (regex)
The By REGEX pattern command gives maximum flexibility. You can set any search pattern, for example:
| Delete cells that… | Pattern |
|---|---|
| contain only digits | ^\d+$ |
| are an e-mail address | ^[\w\.-]+@[\w\.-]+\.\w+$ |
| contain an e-mail anywhere | @ |
| contain a year from 1900 to 2099 | \b(19|20)\d{2}\b |
| contain any Latin letter | [a-zA-Z] |
It’s the perfect tool for complex scenarios where standard filters are not enough. The syntax and ready-made patterns are in the article on regular expressions in Excel.
Why !SEMTools
- It works right on the selected range, with no helper columns.
- A complete set of options: empty cells, lists, words, regex.
- Instant execution without complex formulas and filters.
To delete not the cells but duplicates, see how to remove duplicates, and for cells with digits, how to delete cells containing numbers.
Delete cells by text, a list of values, whole words or a regex pattern in a couple of clicks, without formulas or macros.
!SEMTools solves this and hundreds of other data cleaning tasks!
Summary
I hope you found what you were looking for on this page. Find All and the filter cover one-off tasks, Go To Special handles blank cells, and FILTER in Excel 365 gives a cleaned copy. For regular data cleaning, the !SEMTools procedures delete cells by content right in the selected range.