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:
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:
- From the Developer tab, select Visual Basic.
- 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.
- Make sure ‘ThisWorkbook’ is selected under the VBA project with the same name as your Excel workbook.
- Click Insert->Module. A new module window should open up.
- Now you can start coding. Copy the above code script and paste into the module code window.
- 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:
- Select the range of cells that you want to work with.
- Select the Developer tab.
- Click on the Macros button (under the Code group).
- 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 (or module) named ‘select_rows_with_given_string’.
- Click Run.
- You will get an input box asking you to enter your search string.
- 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’.
- Click OK.
- All the rows containing your search string should now be 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.
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:
- Click on the header of any column in the range you want to work on.
- Click on the Data tab and select the Filter button (You’ll find it under the ‘Sort & Filter’ group.
- You should now see a small arrow button on every cell of the header row.
- These buttons are meant to help you filter your cells. You can click on any arrow to choose a filter for the corresponding column.
- In this example, we want to search for titles in the ‘Books’ column. So click on the arrow next to the ‘Books’ header.
- 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.
- Click OK.
- You should now be able to see only those rows that contain the word ‘King’ in the Books column.
- 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.
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:
- How to Add a Total Row in Excel Table
- How to Select Multiple Rows in Excel
- How to Hide Rows based on Cell Value in Excel
- How to Delete Filtered Rows in Excel (with and without VBA)
- How to Highlight Every Other Row in Excel (Conditional Formatting & VBA)
- How to Unhide All Rows in Excel with VBA
- How to Set a Row to Print on Every Page in Excel
- How to Select Every Other Cell in Excel (Or Every Nth Cell)