XL: Extracting Exact Match of Text from a Database

 Last reviewed: February 2, 1998
Article 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 3.00
4.0 4.00 5.00 5.00a 5.00c 7.00 7.00a 97 98
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

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.