Delete rows in Excel based on one or multiple conditions

The tools in this article are available in the free version of !SEMTools, Lite and Pro.

How do you delete rows in an Excel table by condition when there are hundreds of them and more than one condition? With built-in tools, it’s filter, select, delete, and again for every condition. I’ll cover those briefly and spend most of the article on the range deletion tools in !SEMTools: select a column, pick a condition in the menu, and every row that matches it is deleted entirely.

If you need to delete only the cells and shift the rest up rather than remove whole rows, see how to delete cells by content.

Delete rows based on a condition with built-in Excel tools

Built-in methods exist, and for a one-off task with one condition they’re enough:

• Filter. Turn it on with Ctrl+Shift+L, then in the column choose Text Filters → Contains / Does Not Contain / Begins With. Select the filtered rows, go to Home → Delete → Delete Sheet Rows and clear the filter.
• Find and Replace. Ctrl+F → Find All, press Ctrl+A in the results list and close the dialog: the found cells stay selected. Then Home → Delete → Delete Sheet Rows.
• Sort. Sort the table by the column so the unwanted rows end up together, and delete them in one block.

To be fair, the biggest drawback of the filter is that it can’t handle conditions like “has Latin letters”, “has no digits at all” or “longer than 50 characters”. For those, you need a helper column with a formula, a filter on TRUE and then the deletion. For example, a check for digits with SEARCH and SUMPRODUCT:

=SUMPRODUCT(--ISNUMBER(SEARCH(ROW($1:$10)-1,A2)))>0

The formula searches cell A2 for each digit from 0 to 9 and returns TRUE if it finds at least one. Latin letters work the same way, with a search for every letter from A to Z:

=SUMPRODUCT(--ISNUMBER(SEARCH(CHAR(ROW($65:$90)),A2)))>0

And text length is checked with LEN, for example longer than 50 characters:

=LEN(A2)>50

Any of these formulas has to be filled down the whole column, filtered, the rows deleted, and then the helper column removed too. In Excel 365, the FILTER function can build a cleaned copy of the table instead, but that’s a new table next to the old one, not the original.

Delete table rows in 2 clicks with !SEMTools

All tools in this section work the same way: you select one column where the condition is checked and choose the condition in the menu. The add-in goes through the cells from the bottom up and deletes the entire sheet row whenever a cell matches. No formulas, no filters, no helper columns.

The menu is on the !SEMTools tab: DELETE → Ranges → Rows on criteria in selected column. It has three groups of conditions:

• If cell does NOT contain… — anything (blank), alphanumeric characters, Latin characters, numbers, a specific word, words from a list;
• If cell CONTAINS… — Latin chars, numbers, 1 word, any word from a custom list, words containing only digits;
• If characters length is… — less than or more than a given number of characters.

A few rules shared by all the tools:

• Select more than one cell and only one column. Otherwise the add-in asks you to “Select more than 1 cell”.
• You can select the whole column by clicking its letter: the add-in trims the selection to the last filled cell.
• The entire sheet row is deleted, not just the table row. If other data sits to the left or right on the same rows, it goes too.
• Better leave the header row out of the selection: it’s checked like any other cell.

Important: deleting rows can’t be undone. Ctrl+Z doesn’t work after macros, and the !SEMTools undo button restores cell values, not deleted rows. Before running it on important data, save the file or copy the sheet.

Remove rows with blank cells or no letters and digits

Two tools in the If cell does NOT contain… group clean the table of rows where the column effectively has no data:

• Anything deletes the row if the cell is blank. A space already counts as content, so such a cell isn’t blank.
• Alphanumeric characters deletes the row if a non-blank cell has no letter and no digit: only punctuation, spaces, dashes, symbols like “###” or “—”. Letters of any alphabet count, not just English. Blank cells are left alone by this tool.

If you need to delete rows that are blank across the whole width rather than in one column, there are separate tools for that: see how to delete empty rows in Excel on a sheet or in the whole workbook.

Remove rows with or without Latin letters

Latin letters are the English letters A–Z in any case. There’s one tool in each group:

• If cell CONTAINS… → Latin chars deletes the row if the cell has at least one Latin letter;
• If cell does NOT contain… → Latin characters deletes the row if the cell has no Latin letters at all.

A typical task is a multilingual product feed or keyword export: keep only rows written in English, or drop the rows in other scripts, such as Chinese, Arabic or Greek. Keep in mind that for the second tool a blank cell also “has no Latin letters”, so such a row is deleted.

Remove rows with digits, without digits or with numbers

There are three tools here, and the difference matters:

• If cell CONTAINS… → Numbers deletes the row if the cell has at least one digit anywhere: “iPhone 15”, “SKU A-1024”, “2 pcs”.
• If cell does NOT contain… → Numbers deletes the row if the cell has no digits at all. Blank cells also match.
• If cell CONTAINS… → Words → Containing only digits deletes the row only if the cell has a separate word that is a number, surrounded by spaces: “15”, “2024”, “3.5”. “iPhone15” or “A-1024” don’t trigger it.

“Containing only digits” is handy for cleaning search queries: rows with years, model numbers and quantities go, while queries where the digits are glued to letters stay.

Delete a row if a cell contains or doesn’t contain a word

These tools look for the whole word, not a fragment: searching for “cat” won’t delete the row “catalog request”. Case doesn’t matter. You can also enter a phrase, and the add-in will look for exactly that sequence of words.

• If cell CONTAINS… → Words → 1 word asks for the word and deletes every row that has it;
• If cell does NOT contain… → Words… → Specific word deletes every row that doesn’t have the word. In effect, “keep only the rows with this word”.

What about the last row? Words are split by spaces, so “(wholesale)” with brackets is a different word for the add-in. To keep punctuation from getting in the way, turn on the Ignore punctuation check box in the first group of the !SEMTools tab (its name shows in the tooltip): periods, commas and brackets then act as word separators, while the text in the cells stays unchanged.

Drop rows by a word list: any or none

With several words, you don’t have to run the tool for each one. Put the words in a column on any sheet, one word per cell, and choose:

• If cell CONTAINS… → Words → Any word from custom list deletes the row if the cell has at least one word from the list;
• If cell does NOT contain… → Words… → Words from list deletes the row if the cell has none of the words from the list.

First select the column to check, then run the tool: it asks you to select the range with the list. Words are compared whole and case-insensitively.

This is the classic way to clean a keyword list of negative keywords: a list like “free, download, diy, reviews” removes irrelevant queries in one pass. Two details. The list must contain single words; a two-word phrase won’t match. And punctuation stuck to a word in a cell breaks the match: “download,” and “download” are different words. Before cleaning, remove punctuation from the column being checked or from its copy.

Get rid of rows by text length in a cell

The If characters length is… group deletes rows by the number of characters in the cell, spaces included:

• Less than asks for a number N and deletes rows where the cell has fewer than N characters;
• More than deletes rows where the cell has more than N characters.

Exactly N characters is deleted by neither tool. Typical tasks: drop very short queries of one or two characters, weed out titles longer than 60 characters, find descriptions cut off during an export. A blank cell has a length of 0, so Less than with N ≥ 1 also deletes rows with blank cells.

Delete rows based on multiple conditions

Each tool checks one condition in one column, but they’re easy to combine:

• Any of the conditions (OR). Run the tools one after another: first delete the rows with Latin letters, then the ones shorter than 3 characters, then by the negative keyword list. A row that matches at least one condition is gone.
• Conditions on different columns. Select one column and run its condition, then the other: the remaining rows are checked against the second column.
• Several words at once. Instead of several runs, use a word list: it’s one “any word from the list” condition.
• All conditions at once (AND). If you need to delete only the rows where both conditions hold, a helper column with AND and a filter on TRUE is more convenient: the add-in tools delete by each condition separately.

By the way, if you need to find and mark rows by several conditions rather than delete them, see how to find cells by several conditions.

Why the wrong rows were deleted: common mistakes

• The header row is gone. The header got into the selection and matched the condition: “Query” has no digits. Start the selection from the second row.
• Data of a neighboring table disappeared. The entire sheet row is deleted. Move other tables to a separate sheet or below the one you’re processing.
• Blank rows were deleted, though you didn’t ask. “No Latin letters”, “no digits” and “length less than N” are all true for a blank cell. If blank rows must stay, fill the blank cells with a temporary placeholder that doesn’t match the condition (0 for “no digits”), and clear it after the deletion.
• The word is there, but the row wasn’t deleted. A punctuation mark is stuck to the word, or the word is in another form: “wholesaler” isn’t “wholesale”. Turn on Ignore punctuation or add the word forms to the list.
• The wrong rows are gone and you need them back. Undo won’t return deleted rows, only a saved copy of the file will. So save the file first.

Summary: the fastest way to delete rows by condition

I hope you found what you were looking for on this page and were able to remove the extra rows from your table. A filter works for a one-off simple condition like “contains a word”. For Latin letters, digits, text length and word lists it needs helper formulas, while with !SEMTools you select the column and pick the condition in the menu, even if there are several conditions. You may also be interested in related operations: delete a list of words from text without touching the rows, and remove duplicates without shifting rows.