XL: Extracting Exact Match of Text from a Database
ID: Q94935
|
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 and 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, to filter records in a list by using the Advanced
Filter command (Excel versions 5.0 or later) or to extract records in a
database (Excel versions 2.2, 3.0, and 4.0) that contain an exact match to
a text string, the text to be matched must be preceded by an equal sign in
the criteria range.
Microsoft Excel Versions 4.0 and Later
In Microsoft Excel versions 4.0 and later, use the following format in the
appropriate criteria cell
'=text string
where "text string" is the string you want to match (without the quotation
marks).
NOTE: The syntax for Microsoft Excel versions 4.0 and later is simpler
than in earlier versions of Microsoft Excel because you can use an
apostrophe.
Microsoft Excel Versions 2.2 and 3.0
In Microsoft Excel versions 2.2 and 3.0 use the following format in the
appropriate criteria cell
="=text string"
where "text string" is the string you want to match (WITH the quotation
marks).
MORE INFORMATION
To perform the same operation from a command macro, use the FORMULA
function to convert a cell with a text string (for example a cell
containing the text string "Roy") to an exact match criteria (such as the
criteria "=Roy").
On a Microsoft Excel version 4.0 macro sheet, use the following formula
=FORMULA("'="&ACTIVE.CELL()&"")
where ACTIVE.CELL is the cell containing the text string ("Roy" in the
example above) in the criteria range.
In Microsoft Excel 2.20 and 3.0, use the following formula
=FORMULA("=""="&ACTIVE.CELL()&"""")
where ACTIVE.CELL is the cell containing the text string ("Roy" in the
example above) in the criteria range.
If the criteria is entered as "text string" without the equal sign,
Microsoft Excel will extract all records that contain the text string at
the beginning of the record. For example, "Roy," "Royale," and "Royette"
all meet the criteria of "Roy."
REFERENCES
"Microsoft Excel User's Guide 1," version 4.0, pages 340-343
"Microsoft Excel User's Guide," version 3.0, pages 381-384
"Microsoft Excel User's Guide," version 2.2, pages 176-177
Additional query words:
2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 5.00a 5.00c 7.00a 97 98
Keywords :
Version : WINDOWS:2.0,3.0,4.0,4.0a,5.0,5.0c,7.0,7.0a,97; MACINTOSH:2.0,3.0,4.0,5.0,98; os/2:2.2,3.0
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbinfo
Last Reviewed: March 30, 1999