How to copy rows if column contains specific text/value in Excel?

This method explains how to locate cells containing certain text or value in a chosen column by using the Filter command. Once filtered, you can easily select and copy the entire corresponding rows. This method is most useful for small to moderately sized datasets and provides a straightforward visual way to work with your data.

1. Select the column you wish to filter, and then go to the Data tab. Click the Filter button to enable filtering for your data.

2. Click the filter arrow beside the first cell of your selected column. From the menu, choose Text Filters > Contains.

3. In the Custom AutoFilter dialog box that appears, type the specific text or value you want to search for in the box after contains, and then click OK. 

4. After filtering, Excel will display only the rows where the column contains your specified text or value. Select all these filtered rows, and press Ctrl + C to copy them.

5. Navigate to a blank location in your worksheet or another worksheet, and press Ctrl + V to paste the copied rows. 

Notes:

• Make sure filtering does not hide any relevant data before copying.
• If your data set is too large or requires frequent row extraction, consider using formulas or VBA for automation.
• After copying, you may want to clear filters to view your entire dataset again.

While the standard Find command (Ctrl + F) can quickly locate cells with specific content, it falls short if you want to select and copy entire rows instead of just individual cells. Kutools for Excel provides the Select Special Cells feature, which solves this by letting you identify and select full rows where the target cells are located. This approach offers efficiency and reduces manual handling.

1. Select the columns where you want to find cells containing your specific text or value.

2. Click on Kutools in the Ribbon, then choose Select > Select Specific Cells.

3. In the Select Specific Cells dialog box:
(1) Make sure Entire row in the Selection type section is checked;
(2) Under Specific type, set the first dropdown to Contains, and enter the text or value you seek into the next box;
(3) Click OK to confirm your selections.

4. When the second Select Specific Cells dialog box appears, simply click OK to proceed.

The tool will instantly select all rows containing the specified text or value within your selected columns.

5. Press Ctrl + C to copy these rows to your clipboard.

6. Paste the copied rows at your desired location by pressing Ctrl + V. This can be in the existing sheet or in a new one, as needed.

Tips & Notes:

• Kutools streamlines this process and greatly reduces manual effort, especially with very large datasets.
• Double-check the “Contains” condition, as partial matches may be selected. Adjust accordingly by using different conditions if required (such as “Equals”, “Begins With”, etc)..

When you need to compare two columns and extract complete rows where one column’s value matches any value in another column, Kutools for Excel’s Compare Ranges utility is an excellent solution. This can be useful for tasks such as cross-referencing lists, consolidating data, or tracking overlaps between datasets. The following steps will guide you through the operation:

1. Click Kutools > Select > Select Same & Different Cells.

2. In the Select Same & Different Cells dialog box:

(1) Choose the column to check in the Find values in box;
(2) Set the column to compare against in the According to box;
Note: Use the My data has headers option appropriately depending on your data.
(3) Check Same Values in the Find section;
(4) Check Select entire rows;
(5) Click OK to apply.

3. When the Compare Ranges dialog box appears, it will display the number of rows selected. Click OK to close this prompt.

4. Copy the selected rows with Ctrl + C, and paste them where you need using Ctrl + V.

The Split Data utility in Kutools for Excel enables you to copy entire rows to newly created worksheets whenever the specified column matches a text/value. This technique is best for cases where you want to organize your data automatically into separate tabs, such as sorting sales data by region or department. Here’s how to use it:

1. Select the range from which you want to extract and copy rows, then navigate to Kutools Plus > Split Data.

2. In the Split Data into Multiple Worksheets dialog box, do the following:
- Check the Specific column option;
- Select the relevant column name;
- Choose how you want new sheets to be named (e.g., by unique values or predefined rules);
- Click OK to confirm.

After splitting, you’ll find that all relevant rows have been automatically copied into separate worksheets according to your column criteria. This makes further analysis and reporting straightforward.

Considerations:

• Pay attention to the naming conventions for new sheets for easier navigation afterwards.
• Double-check your selections before splitting to avoid missing data.
• This method is highly efficient for recurring tasks such as monthly or departmental reporting.

Excel formulas offer a flexible and reliable way to flag rows that contain certain text or values within a specific column. By combining functions such as SEARCH, ISNUMBER, and IF, you can easily mark rows for further action, such as filtering, copying, or sorting. This method is particularly useful when working with data that changes regularly or for users who prefer formula-based automation.

1. First, insert a new helper column next to your data (for example, column C). In cell C2, enter the following formula:

=IF(ISNUMBER(SEARCH("specific text",A2)), "Copy", "")

This formula checks if cell A2 contains "specific text". If yes, it will return "Copy"; otherwise, it will leave the cell blank.

2. Press Enter to confirm the formula, then drag the fill handle down to apply the formula to all rows.

3. Now you can filter the helper column for the "Copy" value to quickly find all relevant rows. Select all these rows, use Ctrl + C to copy, and then paste them as needed.

Parameter explanation:

• Replace "specific text" with the keyword or value you need to search for.
• Adjust the referenced column (e.g., A2) depending on which column contains your text/value.

Troubleshooting tips:

• If your text contains special characters or spaces, make sure they match exactly in the SEARCH function.
• This formula is not case sensitive. Use FIND instead of SEARCH for case-sensitive searches.
• For multiple keywords, you can nest additional IF or use OR logic as required.

If you work with very large Excel files or need to automate this row extraction task repeatedly, VBA (Visual Basic for Applications) provides a robust solution. With a simple macro, you can automatically scan the target column, identify matching rows, and copy them to another location. This saves time and reduces human error, especially for complex or repetitive tasks.

1. In Excel, access the VBA editor by clicking Developer > Visual Basic. In the new window, go to Insert > Module and paste the following code into the module:

Sub CopyRowsIfContains()
    Dim ws As Worksheet
    Dim wsDest As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim destRow As Long
    Dim searchText As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    Set rng = Application.InputBox("Select column to search", xTitleId, Selection.Address, Type:=8)
    searchText = Application.InputBox("Enter text or value to search for", xTitleId, "", Type:=2)
    
    Set wsDest = Worksheets.Add
    wsDest.Name = "FilteredRows"
    
    destRow = 1
    For Each cell In rng
        If InStr(1, cell.Value, searchText, vbTextCompare) > 0 Then
            ws.Rows(cell.Row).Copy Destination:=wsDest.Rows(destRow)
            destRow = destRow + 1
        End If
    Next cell
End Sub

2 To run the macro, press the Run button (or F5 key) in the VBA editor. A prompt will appear asking you to select the column to search; then enter the specific text or value to find. The macro will create a new sheet named "FilteredRows" and copy all matching rows there for your review or further processing.

Parameter notes:

• You can modify wsDest.Name if you want to rename the result sheet.
• If you need to match entire cell values rather than a substring, consider replacing InStr with If cell.Value = searchText.

How to check or find if cell contains specific string/text/word in Excel?

How to copy cells if column contains specific value/text in Excel?