How to delete rows not containing certain text in Excel?
Many Excel users are familiar with deleting entire rows when they contain specific text. However, you may encounter situations where you need to remove rows that do not contain certain text instead. For example, you might have a large dataset and want to keep only the rows related to a particular keyword, deleting all others. This can be useful for filtering reports, cleaning survey responses, data quality control, and more. While Excel doesn't provide a direct, one-click solution for this, there are several practical methods you can use, each with different advantages depending on your skill level and requirements. This tutorial will guide you through several effective techniques to delete rows that do not contain a specified text string in Excel, including Filter, VBA, Kutools, and formula approaches.
Delete rows not containing certain text with Filter
Delete rows not containing certain text with VBA
Delete rows not containing certain text with Kutools for Excel
Delete rows not containing certain text with Excel Formula (Helper Column)
Delete rows not containing certain text with Filter
Excel's Filter function is a straightforward and accessible way to identify and remove rows that do not contain specific text. This method is especially useful for those who want a quick, visual approach, and is suitable for small to medium-sized data tables. However, it can become less efficient for very large datasets or in scenarios where the target text is highly variable.
1. Select the column that contains the text you want to filter by. Then click Data > Filter to apply filters to your table. See screenshot:
 |
 |
 |
2. Click the filter button (downward arrow) in the header of the relevant column to open the drop-down filter menu. Uncheck all options except for the text you want to keep; this means only rows with the specific text will remain visible. If your text appears as a partial value or substring, consider using the "Text Filters" > "Contains..." option for more flexibility. See screenshot:
3. Now, only the header and the rows containing the specified text will be shown; rows not containing that text are filtered out in the current view. See screenshot：
4. Select all visible rows except for the header (you can click the first row, hold Shift, and click the last row), then right-click and choose Delete Row from the context menu. This action removes the visible (filtered-in) rows. After deleting, only the filtered-out rows (those that do not contain the specified text) may remain hidden.
5. To complete the process, click Data > Filter again to remove the filter, and you'll see that only rows not containing the specified text remain in your worksheet. See screenshot:
 |
|
 |
Note: Before deleting, double-check that only the intended rows are selected. Deleted rows cannot be recovered unless you undo the action (Ctrl+Z). This method does not work well if your table contains merged cells or nested subtotals.
Delete rows not containing certain text with VBA
If you need to process large datasets or repeat the operation frequently, using VBA (Visual Basic for Applications) can vastly speed up the deletion process. The VBA solution lets you automate row removal based on whether cells contain or do not contain particular text strings, making it ideal for users with basic programming skills or for situations where you need to customize the logic (for example, case-sensitive matching).
1. Press Alt + F11 to open the Microsoft Visual Basic for Applications editor window.
2. In the editor window, click Insert > Module to open a new module window. Copy and paste the following VBA code into the module window.
VBA: Delete entire row if cell does not contain certain text.
Sub DeleteRowNoInclude()
'Updateby Extendoffice
Dim xRow As Range
Dim rng As Range
Dim WorkRng As Range
Dim xStr As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xStr = Application.InputBox("Text", xTitleId, "", Type:=2)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 1 Step -1
Set xRow = WorkRng.Rows(i)
Set rng = xRow.Find(xStr, LookIn:=xlValues)
If rng Is Nothing Then
xRow.Delete
End If
Next
Application.ScreenUpdating = True
End Sub
3. Click the Run button. A dialog box will prompt you to select the range to process (such as the column you want to search for the specified text). See screenshot:
4. Click OK, and enter the text you want to use as criteria for deletion in the next pop-up dialog. For best results, enter the text exactly as it appears in your data. The code performs non-case-sensitive (default) search; modify the code if you require case sensitivity. See screenshot:
5. Click OK, and any rows where the cell does not contain the specified text (e.g., "Apple") will be deleted immediately. Ensure you save your work before running VBA macros, as this operation cannot be easily reversed. If unexpected results occur, use Undo (Ctrl+Z) or close the file without saving, then try again with the correct parameters. For advanced users, you can adjust this code for partial matches or multiple criteria as needed.
Delete rows not containing certain text with Kutools for Excel
For users who prefer not to handle manual filtering or VBA, Kutools for Excel offers an efficient, interactive way to quickly select and delete rows not containing specific text with just a few clicks. This is particularly helpful if you're processing data frequently or dealing with multiple search conditions. While Kutools requires installation, it significantly simplifies complex Excel operations and supports batch actions. Its operations are intuitive and beginner-friendly.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Get it Now
1. Select the column in which you want to delete rows that do not contain the specified text, then click Kutools > Select Tools > Select Specific Cells. See screenshot:
2. In the Select Specific Cells dialog box, check Entire row, choose Does not contain in the drop-down, and type the target text in the text box. Alternatively, you can click the range selection icon in the Specific type section to select a reference cell containing the desired text. Double-check your entry for typos or extra spaces, as these will affect selection accuracy.
3. Click OK. The rows not containing the defined text will be automatically selected. Right-click on any of the highlighted row numbers and choose Delete from the context menu. This deletes all selected rows at once. See screenshot:
4. Now, all rows in the selected range that did not contain your specified text are deleted from your worksheet. For more advanced tips and the latest features, see: Select Specific Cells documentation.
Tips: Before deleting, review your selection to avoid accidental loss of needed data, and consider creating a backup copy for safety. Kutools' selection function can be used in combination with other features for even more customized data management.
Delete rows not containing certain text with Excel Formula (Helper Column)
Another highly practical method involves using an Excel formula in a helper column. This approach allows you to quickly identify whether each row contains (or does not contain) certain text, and then filter or delete as needed. This method is suitable for those who are comfortable with formulas and prefer a transparent, auditable process to check the results before deletion. It is especially useful when your search text may appear anywhere within a cell (not just as an exact match).
Advantages: Highly flexible, does not require add-ins or VBA, safe as it does not delete data immediately—the user reviews results first. Disadvantage: Multi-step process and requires some familiarity with Excel formulas and filters.
Instructions:
1. Add a new helper column next to your data. For illustration, assume your text is in column A and your data starts from row 2 (excluding the header).
2. In the first row of your helper column (e.g., B2), input the following formula (replace "text" with your desired string to search):
=ISNUMBER(SEARCH("text",A2))This formula returns TRUE if the cell in A2 contains the given text (as a substring, non-case-sensitive), and FALSE otherwise.
3. Press Enter, then double-click the lower right corner of the cell to fill the formula down to all other rows in your dataset.
4. Use the filter function on the helper column. Click the drop-down arrow, choose FALSE to show only rows not containing your target text. Select all these filtered rows (excluding the header, if applicable), right-click, and select Delete Row.
5. After deleting, clear the filter to restore the view. Verify your results, and remove the helper column if desired.
Parameter and tip notes:
- If you need to match exact cell value (not substrings), modify the formula to
=A2="text". - If your data contains empty cells, ISNUMBER(SEARCH(...)) returns FALSE, and those rows will be treated as not containing the text.
- For case-sensitive matching, replace SEARCH with FIND in the formula.
Troubleshooting: The formula approach is non-destructive until you actually delete rows. Always double-check your TRUE/FALSE results, and consider saving a backup copy before bulk deletions.
Relative Articles:
- Delete entire rows if cell not containing zero in Excel
- Delete rows not matching criteria on another sheet
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in