Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

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.

↑ Back to the top


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.

↑ Back to the top


Keywords: KB214070, kbhowto

↑ Back to the top

Article Info
Article ID : 214070
Revision : 5
Created on : 1/24/2007
Published on : 1/24/2007
Exists online : False
Views : 36