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


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
  • Microsoft Excel 2000
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q214070

SUMMARY

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.

MORE INFORMATION

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.
Modification Type:MajorLast Reviewed:5/17/2005
Keywords:kbhowto KB214070
©2004 Microsoft Corporation. All rights reserved.