How to Select Rows with Specific Text in Excel

by

When you are working with large amounts of data in spreadsheets, you need all the tips and tricks you can get to quickly access your required data.

Oftentimes you might need to work with selected rows in bulk.

For example, you might need to delete rows containing a specific text, or maybe format them a certain way.

In all these cases, if you are to work with them in one single go, you will need to first select these rows all at the same time. Unfortunately, selecting rows with specific text in Excel can be quite a tricky affair.

In this tutorial we will show you two ways in which you can select rows with specific text in Excel:

  • Using VBA
  • Using Data Filters

The first method is quick and easy, but involves a little bit of coding.

If you’re not comfortable with coding, you can opt for the second method.

How to Select Rows with Specific Text in Excel

To demonstrate the two methods in this tutorial, we are going to use the following data:

Dataset with book names

Given the above data, let us say you want to find and select all the rows that contain the word “King” in them.

Here are two ways to tackle this problem.

Using VBA to Select Rows with Specific Text in Excel

This method involves coding in VBA.

We have already prepared the code that you need to use, so all you need to do is just navigate to the Developer window, copy-paste the code, select the data that you want to work on and run the code.

To use this code, you need to first select the data where you want to select rows with specific text and then run this code.

The following code will help you select rows with specific text in Excel.

'This code is developed by Steve from spreadsheetplanet.com
 Sub select_rows_with_given_string()
 Dim Rng As Range
 Dim myCell As Object
 Dim myUnion As Range
 Set Rng = Selection
 searchString = InputBox("Please Enter the Search String")
 For Each myCell In Rng
 If InStr(myCell.Text, searchString) Then
      If Not myUnion Is Nothing Then
          Set myUnion = Union(myUnion, myCell.EntireRow)
      Else
          Set myUnion = myCell.EntireRow
      End If
 End If
 Next
 If myUnion Is Nothing Then
     MsgBox "The text was not found in the selection"
 Else
     myUnion.Select
 End If
 End Sub

The above macro loops through each cell in a range and selects only the rows that contain the text “King”.

Note that the above code is case sensitive. In case you look for the word ‘king’, it will not be able to find it and show you a message box letting you know that it couldn’t find the specified text.

To enter the above code, copy it and paste it in your developer window.

Here’s how to copy and run this code:

  1. From the Developer tab, select Visual Basic.
Click on VIsual Basic
  1. Once your VBA window opens, you will see all your files and folders in the Project Explorer on the left side. If you don’t see the Project Explorer, click on View->Project Explorer.
Project Explorer in the VB Edior
  1. Make sure ‘ThisWorkbook’ is selected under the VBA project with the same name as your Excel workbook.
  2. Click Insert->Module. A new module window should open up.
Insert a module
  1. Now you can start coding. Copy the above code script and paste into the module code window.
Copy the VBA code in the code window
  1. Close the VBA window. 

Your macro is now ready to use.

Note: If you don’t see the Developer ribbon, from the File menu, go to Options. Select Customize Ribbon and check the Developer option from Main Tabs. Finally, Click OK.

Now that we have copied the code in it’s rightful place, we need to run the code.

To run your macro, do the following:

  1. Select the range of cells that you want to work with.
  2. Select the Developer tab.
  3. Click on the Macros button (under the Code group).
Click on Macros
  1. This will open the Macro Window, where you will find the names of all the macros that you have created so far.
Select the Macro that you want to run
  1. Select the macro (or module) named ‘select_rows_with_given_string’.
  2. Click Run.
  3. You will get an input box asking you to enter your search string.
Enter the text for which you want to select all rows
  1. Type in the text you want to search for. In our example, we are looking to select all rows with the word ‘King’ in it. So we type ‘King’.
  2. Click OK.
  3. All the rows containing your search string should now be selected.
Rows with the specified text selected

Now that your required rows are selected you can go ahead and perform whatever actions you need to with them.

You can also use this script and tweak it to suit your own requirements.

Explanation of the Code

So how did this code work? 

In the above code we used the InputBox function to obtain the search string input from the user.

We then used a For-each loop to cycle through each cell in the given range (Rng). If a cell contains the given search string, we add the entire row of that cell to myUnion (a Range variable containing the range of all rows that we want to select).

To find out if a cell contains the given search string, we used the InStr function. Finally we used the command ‘myUnion.Select’ to select all the rows that have been added to myUnion.

In this way, the code selects all rows that contain the search string provided by the user.

Also read: How to Rearrange Rows In Excel

Using Filters to Select Rows with Specific Text in Excel

The VBA method is actually the best way to select rows with specific text in Excel.

However, if the idea of coding or using VBA intimidates you, then there’s an alternative way to get the job done.

This involves using Excel’s handy Filters feature. This feature lets you filter out rows that match a given criterion.

So you can easily use it to see which rows contain your specified text. It will filter out all other rows and show you only the matching ones.

So, you get all your required rows one below the other.

This makes it easy for you to select all the filtered rows in one go and perform subsequent actions on them.

Here are the steps that you need to follow if you want to use filters to select rows with specific text:

  1. Click on the header of any column in the range you want to work on.
  2. Click on the Data tab and select the Filter button (You’ll find it under the ‘Sort & Filter’ group.
Click on the Filter option
  1. You should now see a small arrow button on every cell of the header row.
Filters are applied to the dataset
  1. These buttons are meant to help you filter your cells. You can click on any arrow to choose a filter for the corresponding column.
  2. In this example, we want to search for titles in the ‘Books’ column. So click on the arrow next to the ‘Books’ header.
Click on the Filter icon in the header
  1. This will display a drop-down with different filtering options. In the input box under ‘Text Filters’, type your search string. Since we are looking for the word ‘King’, we will type that into the input box.
Enter the text in the text filter
  1. Click OK.
  2. You should now be able to see only those rows that contain the word ‘King’ in the Books column.
All rows with the specified text are filtered
  1. You can now select the row headers of all the displayed rows altogether. This will cause only the visible rows to get selected. The hidden rows (which do not contain the word ‘King’) will not be selected.
Select all the filtered rows

Now that your required rows are selected, you can go ahead and copy them, delete them or perform whatever tasks you need to with them.

One thing that I like about this method is that it allows you to apply multiple conditions at the same time and then allow you to select the records.

For example, if I want to select all the rows where the book name has the word ‘King’ and the book availability status is Yes, then I can do that. All I need to do is apply two separate filters and then select the rows that remain.

In this tutorial we showed you two tricks to select rows with specific text in Excel.

I hope this was helpful and easy for you to apply.

Some other Excel tutorials you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

1 thought on “How to Select Rows with Specific Text in Excel”

  1. Greetings.
    Thanks a lot! Your VBA code for “Using VBA to Select Rows with Specific Text in Excel”, followed by “delete” task of selected range, runs much faster than other methods of “select-delete” operation.

    Reply

Leave a Comment