How to Delete Rows in Excel with Specific Text: 3 Ways

Method 1 – Using the Find Feature to Delete Rows with Specific Text

Delete all the rows matched with the text “Alan.” We’ll show 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 containing the names “Alan” and “Alan Marsh.” We can use partial matching to remove these two rows.

Steps:

  • From the Home tab, go to Find & Select and then click Find.

Delete Rows using Find Feature of Excel

  • The “Find and Replacedialog box will appear. You can use CTRL + F to open this.
  • Type “Alan” in the Find what: box.

  • Click Find All. Two results will be shown.
  • You need to select those two by using SHIFT + Click.
  • After selecting, click on Close.

Delete Rows using Find Feature of Excel with Partial Text Matching

  • Right-click on any of the selected rows to show the Context Menu bar.
  • Select Delete.

  • Select the Entire row from the dialog box.
  • Click on OK.

Delete Rows with Specific Text using Find Feature of Excel

Rows containing the text “Alan” are not there anymore.
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”).

Steps:

  • Bring up the Find and Replace dialog box by following the previous method.
  • Select Options>>.

Delete Rows using Find Feature of Excel with Full Matched Text

We will only remove the text “Alan”. So we need to –

  • Put a tick in Match entire cell contents.
  • Click Find All.

Notice that only row 6 is selected.

  • Select that result.
  • Click Close.

Delete Rows using Find Feature of Excel with Fully Matched Text

  • Right-click on that result to bring up the Context Menu.
  • Click Delete.

  • Select Entire row.
  • OK.

Delete Rows using Find Feature of Excel with Specific Text

The result will be like this. Only the row with the text “Alan” will be deleted.
The row with “Alan Marsh” will be intact.


Method 2 – Delete Rows with Matching Specific Text Using Filter

We can also use the Excel Filter command to delete rows with matching text. Our dataset contains the Names, Birth Years, and Heights of 10 people.

Delete Rows using Filter Feature of Excel with Specific Text


2.1. Generic Method of Using Filter to Delete Row Containing Specific Text

Delete the row that contains the text “Bruce” using the Filter command of Excel.

Steps:

  • Select the range where you want to apply Filter.
  • We selected the range B4:D14.
  • From the Data tab, select Filter.

See three Excel Filter icons appear in the column header.

Delete Row using Filter Feature in Excel with Specific Text

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 “Brucewill be shown.

  • Right-click on the row to bring up the Context Menu.
  • Delete Row.

Delete Rows Using Filter in Excel

A warning message will appear.

  • Click OK.

Notice there is nothing. We can bring back the other rows by clearing the Filter criteria.

Delete Rows Using Filter Feature in Excel with Specific Text

  • Click the Filter button of the Name column.
  • Select Clear Filter From “Name”.

There is no row with the text “Bruce”.

Delete Rows with Specific Text using Filter


2.2. More Than One Word Matching

Steps:


2.3. Delete Rows with Specific Words and Conditions

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.

Delete Rows in Excel with Specific Text and Criteria

Steps:

  • 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.

Delete Rows Using Filter Feature of Excel with Specific Text and Criteria

We will get the following result.

  • From the Name Filter icon, select “Gina”.
  • Press OK.

Delete Rows with Specific Word and Condition

  • Select the rows and right-click to open the Context Menu.
  • Select Delete Row.
  • Click OK.

  • Remove the Filter to show all the data.

Get the dataset without the text “Gina” born after “1990“.

Delete Rows with Specific Word and Condition 2


Method 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:

  • Press ALT + F11, or from the Developer tab select Visual Basic to open the VBA window.
  • Go to Insert, Module.

Delete Rows Containing Specific Word by Applying VBA

  • 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

  • Run the code from Run Sub/UserForm.

Delete Rows Containing Specific Word by Applying VBA

You can press F5 to do that.
The rows containing the word “East” are deleted from the dataset.

Delete Rows Containing Specific Text by Applying VBA


Download Practice Workbook

You can download the Excel file from this link.


Related Articles


<< Go Back to Delete Multiple Rows Based on Condition | Delete Rows | Rows in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo