Excel If Contains Then Copy

The ability to automate tasks in Excel can significantly enhance productivity, and one common requirement is to copy data from one cell to another based on certain conditions. The "If Contains Then Copy" functionality is a powerful tool in Excel that allows you to achieve this. It essentially checks if a cell contains a specific text or value and, if it does, copies data from one cell or range to another.

To implement the "If Contains Then Copy" functionality in Excel, you can use various methods, including formulas, macros, or even the built-in functions like IF and IFERROR combined with other functions. Here's how you can do it:

Using Formulas

Excel formulas provide a straightforward way to perform conditional checks and copy data. One of the most common formulas used for this purpose is the IF function.

Example 1: Using IF Function

Suppose you have a list of names in column A and you want to copy the names that contain a specific string, say "John", to column B. You can use the following formula in cell B1 and then drag it down to apply it to all cells in column A:

=IF(ISNUMBER(SEARCH("John",A1)),A1,"")

This formula searches for "John" within the text of cell A1. If "John" is found, it returns the value of A1; otherwise, it returns an empty string.

Example 2: Using IF and ISNUMBER/SEARCH for Multiple Conditions

If you need to check for multiple conditions, you can nest IF functions or use the IF function with the SEARCH function for each condition. For instance, to copy names that contain either "John" or "Jane", you can use:

=IF(OR(ISNUMBER(SEARCH("John",A1)),ISNUMBER(SEARCH("Jane",A1))),A1,"")

Using VBA Macros

For more complex operations or when dealing with large datasets, using VBA (Visual Basic for Applications) macros can be more efficient. Macros allow you to automate repetitive tasks and can be triggered by various events, including the change of a cell's value.

Example: VBA Macro to Copy Cells Containing Specific Text

Open Excel and go to the Visual Basic Editor by pressing Alt + F11 or navigating to Developer > Visual Basic.
In the Visual Basic Editor, insert a new module by right-clicking on any of the objects for your workbook in the "Project" window and choosing Insert > Module.
Paste the following code into the module:

Sub CopyIfContains()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") 'Change to your sheet name
    
    Dim i As Long
    For i = 1 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        If InStr(1, ws.Cells(i, "A").Value, "John") > 0 Then
            ws.Cells(i, "B").Value = ws.Cells(i, "A").Value
        End If
    Next i
End Sub

Replace "Sheet1" with the name of your sheet and "John" with the text you're searching for.
Run the macro by pressing F5 while in the Visual Basic Editor with the module containing the macro code active, or close the Visual Basic Editor and run it from Excel's Developer tab.