Microsoft KB Archive/94935

{|
 * width="100%"|

XL: Extracting Exact Match of Text from a Database

 * }

-

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 &quot;text string&quot; 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

  =&quot;=text string&quot;

where &quot;text string&quot; 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 &quot;Roy&quot;) to an exact match criteria (such as the criteria &quot;=Roy&quot;).

On a Microsoft Excel version 4.0 macro sheet, use the following formula

  =FORMULA(&quot;'=&quot;&ACTIVE.CELL&&quot;&quot;)

where ACTIVE.CELL is the cell containing the text string (&quot;Roy&quot; in the example above) in the criteria range.

In Microsoft Excel 2.20 and 3.0, use the following formula

  =FORMULA(&quot;=&quot;&quot;=&quot;&ACTIVE.CELL&&quot;&quot;&quot;&quot;)

where ACTIVE.CELL is the cell containing the text string (&quot;Roy&quot; in the example above) in the criteria range.

If the criteria is entered as &quot;text string&quot; without the equal sign, Microsoft Excel will extract all records that contain the text string at the beginning of the record. For example, &quot;Roy,&quot; &quot;Royale,&quot; and &quot;Royette&quot; all meet the criteria of &quot;Roy.&quot;