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))))
| A | B | C | |
|---|---|---|---|
| 1 | Data | Without “test” | |
| 2 | test order | Order 42=FILTER(A2:A7,NOT(ISNUMBER(SEARCH("test",A2:A7)))) | |
| 3 | Order 42 | delivery | |
| 4 | Test | refund | |
| 5 | delivery | ||
| 6 | latest news | ||
| 7 | refund |
SEARCH finds “test” in any case and inside words, so “latest news” goes too. To match only the whole word, use REGEXTEST(A2:A7,"\btest\b",1) in Excel 365 instead of ISNUMBER(SEARCH(…)).
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.
Delete cells by content in 2 clicks with !SEMTools
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
There is no separate command for deleting single empty cells with a shift: Go To Special, described above, does exactly that. For whole empty rows and columns, the add-in has ready commands:
- DELETE → Ranges → Blank rows removes empty rows on the current sheet or on all sheets.
- DELETE → Ranges → Empty columns does the same for columns.
Delete cells containing any value from a list
The By custom criteria list group is useful when you have a list of values to delete. Put the list in a column: the add-in asks you to select it.
- Containing clears the cells where any value from the list occurs in any part of the text.
- Starts with clears only the cells where a value from the list is at the start of the text.
- Ends with clears 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 clears the cells containing the word you type in the text box.
- Any from word list clears the cells containing at least one word from the specified range.
Delete cells by regular expression (regex)
The By RegExp 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.
Advantages over the built-in tools
- 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: which way to delete cells by content
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.