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.
|