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 that contains sales information for different locations. The dataset has 3 columns: Name, Location, and Sales.
How to Delete Rows in Excel with Specific Text: 3 Ways
1. Using the 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 partially 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 the 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, that 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.
The row with “Alan Marsh” will be intact.
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 rows that contain 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 the Filter criteria.
- Click on the Filter button of the 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 the text “Gina” alongside “Bruce”. To do 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 Words and Conditions
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 the Born column.
- From Number Filters, select Greater Than…
- Put 1990 in the “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“.
3. Delete Rows Containing Specific Words 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 the 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.
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.
Download Practice Workbook
You can download the Excel file from this link.
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.
Related Articles
- How to Delete All Rows Not Containing Certain Text in Excel
- How to Delete Rows Based on Another List in Excel
- How to Delete Blank Rows in Excel
- Delete Row If Cell Contains Specific Values in Excel
- How to Delete Row If Cell Is Blank in Excel
- How to Delete Empty Rows at Bottom in Excel
- How to Delete All Rows Below a Certain Row in Excel?
- How to Remove Highlighted Rows in Excel?
