Description of filtering and extracting a record that contains a text string in Excel

In Microsoft Excel, you can use computed criteria to filter or extract all records from a list or database where a particular text string is contained in a given column.

To filter or extract all records in a column that contains a specific text string, follow the steps in one of the following examples.

Example 1: Search Is Not Case-Sensitive

1. Start Excel, and then create the following spreadsheet:

      A1: Name      B1:       C1: Name
      A2: John      B2:
      A3: Sue
      A4: Maryjo
      A5: Bill
   					

2. Type the following equation into cell B2 to search for the text string "Jo"
   =SEARCH("Jo",A2)
   where A2 is the first cell (not including the field name) in the column where the text string may occur.
3. Select the list range A1:A5.
4. On the Data menu, point to Filter, and then click Advanced Filter.
5. Click OK in the dialog box telling you that no headers are detected.
6. Under Action, click Copy to another location.
7. In the Criteria range box, type B1:B2.
   
   NOTE: The criteria name in the first row of the criteria range (cell B1) can be any name except the name of a database field; if you use the name of a database field, Excel interprets the criteria as comparison criteria. You may also leave the cell blank, as shown in this example.
8. In the Copy to box, type C1, and then click OK.
   
   John and Maryjo appear in the list.

Example 2: Search Is Case-Sensitive

1. Start Excel, and then create the following spreadsheet:

      A1: Name      B1:       C1: Name
      A2: John      B2:
      A3: Sue
      A4: Maryjo
      A5: Bill
   					

2. Type the following equation into cell B2 to search for the text string "jo"
   =FIND("jo",A2)
   where A2 is the first cell (not including the field name) in the column where the text string may occur.
3. Select the list range A1:A5.
4. On the Data menu, point to Filter, and then click Advanced Filter.
5. Click OK in the dialog box telling you that no headers are detected.
6. Under Action, click Copy to another location.
7. In the Criteria range box, type B1:B2.
8. In the Copy to box, type C1, and then click OK.
   
   Only Maryjo appears in the list.

Example 3: Search For An Exact Text String

1. Start Excel, and then create the following spreadsheet:

      A1: Name      B1:       C1: Name
      A2: John      B2:
      A3: Sue
      A4: Maryjo
      A5: Bill
   					

2. Type the following equation into cell B2 to search for the text string "John"
   =A2="John"
   where A2 is the first cell (not including the field name) in the column where the text string may occur.
3. Select the list range A1:A5.
4. On the Data menu, point to Filter, and then click Advanced Filter.
5. Click OK in the dialog box telling you that no headers are detected.
6. Under Action, click Copy to another location.
7. In the Criteria range box, type B1:B2.
8. In the Copy to box, type C1, and then click OK.
   
   Only John appears in the list.