Microsoft KB Archive/214070

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

Article ID: 214070

Article Last Modified on 1/24/2007

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 2001 for Mac
 * Microsoft Excel 98 for Macintosh

-



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
  Start Excel, and then create the following spreadsheet:   A1: Name      B1:       C1: Name A2: John     B2: A3: Sue A4: Maryjo A5: Bill  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. Select the list range A1:A5. On the Data menu, point to Filter, and then click Advanced Filter. Click OK in the dialog box telling you that no headers are detected. Under Action, click Copy to another location.</li> 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.</li> In the Copy to box, type C1, and then click OK.

John and Maryjo appear in the list.</li></ol>

Example 2: Search Is Case-Sensitive
<ol>  Start Excel, and then create the following spreadsheet: <pre class="fixed_text">  A1: Name      B1:       C1: Name A2: John     B2: A3: Sue A4: Maryjo A5: Bill </li> 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.</li> Select the list range A1:A5.</li> On the Data menu, point to Filter, and then click Advanced Filter.</li> Click OK in the dialog box telling you that no headers are detected.</li> Under Action, click Copy to another location.</li> In the Criteria range box, type B1:B2 .</li> In the Copy to box, type C1, and then click OK.

Only Maryjo appears in the list.</li></ol>

Example 3: Search For An Exact Text String
<ol>  Start Excel, and then create the following spreadsheet: <pre class="fixed_text">  A1: Name      B1:       C1: Name A2: John     B2: A3: Sue A4: Maryjo A5: Bill </li> 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.</li> Select the list range A1:A5.</li> On the Data menu, point to Filter, and then click Advanced Filter.</li> Click OK in the dialog box telling you that no headers are detected.</li> Under Action, click Copy to another location.</li> <li>In the Criteria range box, type B1:B2 .</li> <li>In the Copy to box, type C1, and then click OK.

Only John appears in the list.</li></ol>

Additional query words: XL2000 xl2002 xl2001 xl97 xl98 XL2003 XL

Keywords: kbhowto KB214070

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.