XL: Filtering/Extracting a Record That Contains a Text String

ID: Q104090

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0, 5.0c
Microsoft Excel for OS/2, versions 2.2, 3.0
Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0, 5.0a
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

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

Use the following example data with the appropriate procedure to filter or extract all records where the Name column contains the text string "Jo" (without the quotation marks).

Example Data


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

Microsoft Excel Version 5.0 and Later

To use this method, follow these steps:

To extract all records where a particular text string occurs in a column, enter the following computed criteria

B2: =SEARCH("Jo",A2)

where "Jo" is the text string to search for, and A2 is the first cell (not including the field name) in the column where the text string may occur. If you want to your search to be case sensitive, use FIND("Jo",A2).

To extract all records from a column that contain an exact match to a text string, enter the following computed criteria:

B2: =A2="John"

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, Microsoft Excel will interpret the criteria as comparison criteria. You may also leave the cell blank, as is shown here.

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, select the Copy To Another Location option. In the Criteria Range box, type "B1:B2" (without the quotation marks). In the Copy To box, type "C1" (without the quotation marks) and click OK.

If you used the first computed criteria shown above, the names John and Mary Jo appear in your filtered list. If you used the second computed criteria, only the name John appears.

Microsoft Excel Versions 2.x, 3.0, and 4.0

To use this method, follow these steps:

Select cells A1:A5 and click Set Database on the Data menu.

Select cells B1:B2 and click Set Criteria on the Data menu.

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, Microsoft Excel will interpret the criteria as comparison criteria. You may also leave the cell blank, as is shown here.

Select cell C1 and click Set Extract on the Data menu (Microsoft Excel 3.0 and 4.0 only).

To extract all records where a particular text string occurs in a column, enter the following computed criteria

B2: =SEARCH("Jo",A2)

where "Jo" is the text string to search for, and A2 is the first cell (not including the field name) in the column where the text string may occur. If you want to your search to be case sensitive, use FIND("Jo",A2).

To extract all records from a column that contain an exact match to a text string, enter the following computed criteria:

B2: =A2="John"

Select cell C1. This step is required in Microsoft Excel 2.x; it is optional in Microsoft Excel 3.0 and 4.0.

Click Extract on the Data menu. Click OK in the dialog box that appears on your screen.

If you used the first computed criteria shown above, the names John and Mary Jo appears in your extract range. If you used the second computed criteria, only the name John appears.

Additional query words: 2.20 4.00a 5.00a 5.00c 7.00a 97 98 XL98 XL97 XL7 XL5 XL4 XL3

Keywords :
Version : WINDOWS:2.0,3.0,4.0,5.0,5.0c,7.0,7.0a,97: MACINTOSH:2.0,3.0,4.0,5.0,98
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbinfo