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