Rafiul Haq
We often need to delete rows with specific text in Microsoft Excel. I have explained 3 methods on how to delete rows in Excel with specific text in this article. The methods are super easy to follow.
We are going to use a sample dataset to explain the methods clearly. We’ve taken a dataset of a particular shop where it contains sales information of different locations. The dataset has 3 columns: Name, Location, and Sales.
Download Practice Workbook
You can download the Excel file from this link.
3 Ways to Delete Rows with Specific Text in Excel
1. Using Find Feature to Delete Rows with Specific Text
In this section, we will delete all the rows matched with the text “Alan“. I will show you deletion for both Partial Matching and Full matching using the Find feature of Excel.
1.1. Delete Rows with Partial Matching Text in Excel
In this, we will delete rows with partial matched text. In our dataset, we have two rows that contain the names “Alan” and “Alan Marsh”. We can use partial matching to remove these two rows.
To do so follow the explained steps.
Steps:
- From the Home tab go to Find & Select and then click Find.
- Then the “Find and Replace” dialog box will appear. Alternatively, you can use CTRL + F to open this.
- Now type “Alan” in the Find what: box.
- Click on Find All. Two results will be shown.
- You need to select those two, by using SHIFT + Click.
- After selecting, click on Close.
- Right click on any of the selected rows to show the Context Menu bar.
- Then, select Delete…
- Select Entire row from the dialog box.
- Then click on OK.
Rows containing the text “Alan” are not there anymore.
Finally, you can see the result below.
1.2. Using Find Feature to Delete Rows with Full Matching Text
From the same dataset, we will remove the text “Alan” only (not “Alan Marsh”). To do that follow these steps.
Steps:
- Bring up the Find and Replace dialog box by following the previous method.
- Select Options>>.
We will only remove the text “Alan”. So we need to –
- Put a tick in Match entire cell contents.
- Click on Find All.
Now notice, only row 6 is selected.
- Select that result.
- Click on Close.
- Now Right click on that result to bring up the Context Menu.
- Click on Delete…
- Select Entire row.
- Then OK.
The result will be like this. Only the row with the text “Alan” will be deleted.
Row with “Alan Marsh” will be intact.
Related Content: How to Delete Multiple Rows in Excel with Condition (3 Ways)
2. Delete Rows with Matching Specific Text Using Filter
We can also use the Excel Filter command to delete rows with matching text. We have a dataset containing the Name, Birth Year, and Height of 10 people.
2.1. Generic Method of Using Filter to Delete Row Containing Specific Text
We will delete the row that contains the text “Bruce” using the Filter command of Excel.
Steps:
First, We need to enable Excel Filter. To do that:
- Select the range where you want to apply Filter.
- We selected the range B4:D14.
- From the Data tab, select Filter.
We will see three Excel Filter icons appear in the column header.
We want to remove the row that contains the text “Bruce”.
- Select the Name column and Expand Filter icon.
- Uncheck (Select All).
- Check “Bruce”.
- Then OK.
Row with “Bruce” will be shown.
- Right Click on the row to bring up the Context Menu.
- Then Delete Row.
A warning message will appear.
- Click on OK.
Notice there is nothing. We can bring back the other rows by clearing Filter criteria.
- Click on the Filter button of Name Column.
- Then select Clear Filter From “Name”.
We can see the result. There is no row with the text “Bruce”.
2.2. More Than One Word Matching
If you want you can remove more than two texts by using similar steps. In this method, I’m going to explain it to you.
For example, we want to remove the rows with text “Gina” alongside with “Bruce”. For doing so follow these
Steps:
- Select “Gina” and “Bruce” in the Excel Filter dropdown box.
- Follow the previous method 2.1 to Delete multiple rows.
2.3. Delete Rows with Specific Word and Condition
We can remove rows with matching text and criteria too. We have a similar dataset from above. However, this time we have three people named “Gina”. Now we want to remove the rows containing the name “Gina” and those born after 1990.
Steps:
We will first Filter the people born after 1990.
- Click on the Filter icon of Born column.
- From Number Filters, select Greater Than…
- Put 1990 in “is greater than” box.
- Press OK.
We will get the following result.
- Now from the Name Filter icon select “Gina”.
- Press OK.
- Select the rows and Right Click to open the Context Menu.
- Then select Delete Row.
- Click OK.
- We will again remove the Filter to show all the data.
Finally, we will get the dataset without the text “Gina” born after “1990“.
Read More: How to Filter and Delete Rows with VBA in Excel (2 Methods)
Similar Readings:
- How to Delete Rows Based on Another List in Excel (5 Methods)
- Excel VBA: Delete Row If Cell Is Blank (A Complete Guide)
- How to Delete Multiple Rows in Excel Using Formula (5 Methods)
- Delete Unfiltered Rows in Excel Using VBA (4 ways)
- How to Delete Infinite Rows in Excel (5 Easy Ways)
3. Delete Rows Containing Specific Word by Applying VBA
Our dataset contains sales rep, their region, and total sales volume. We want to remove the “East” Region from this data set. VBA can be used to remove rows with matched text.
Steps:
- Firstly, Press ALT + F11 or from Developer tab select Visual Basic to open the VBA window.
- Secondly, Go to Insert then Module.
- Thirdly, Write the following code in the Module.
Sub DeleteRowsWithSpecificText()
Dim x1 As Range
For Each x1 In Range("c5:c14")
If x1.Value = "East" Then
x1.EntireRow.Delete
End If
Next x1
End Sub
- Finally, Run the code from Run Sub/UserForm.
Alternatively, you can press F5 to do that.
The rows containing the word “East” are deleted from the dataset.
Related Content: Excel Shortcut to Delete Rows (With Bonus Techniques)
Practice Section
We have included extra datasets in the Excel Sheet. You may practice the 3 methods to delete rows and understand the methods more clearly.
Conclusion
We have used three methods to delete rows in Excel with specific text. We have used Excel Find, Filter, and VBA features to achieve our goal. You can download our worksheet to practice the methods. If you face any problems, you can ask questions in our comment section.
