Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. XL: How to Extract an Exact Match of Text from a Database

XL: How to Extract an Exact Match of Text from a Database

View products that this article applies to.

Summary

In Microsoft Excel, to filter records in a list by using the Advanced Filter command, the text to be matched must be preceded by an equal (=) sign in the criteria range. Use the following format in the appropriate criteria cell
'=text string
where text string is the string that you want to match.

↑ Back to the top

More information

To use the Advanced Filter command to extract an exact match, follow these steps:
  1. Start Microsoft Excel.
  2. Create the following spreadsheet:
       A5: NAME   B5: MONTH   C5: SALES
   A6: Roy    B6: Jan     C6: 1000
   A7: Jim    B7: Jan     C7: 1000
   A8: Fred   B8: Jan     C8: 1000
   A9: Roy    B9: Feb     C9: 1000
   A10:Jim    B10:Feb     C10:1000
   A11:Fred   B11:Feb     C11:1000
  3. Select cells A5:C5.
  4. On the Edit menu, click Copy.
  5. Select cell A1.
  6. On the Edit menu, click Paste.
  7. In cell A2, type '=Roy.
  8. Select cells A5:C11.
  9. On the Data menu, point to Filter, and then click Advanced Filter.
  10. Click the icon in the Criteria range box to shrink the Advanced Filter dialog box.
  11. Select cells A1:A2.
  12. Click the icon in the Criteria range box again to expand the Advanced Filter dialog box.
  13. Click OK to sort on Roy.

↑ Back to the top

References

For more information about using the advanced filter, click Microsoft Excel Help on the Help menu, type filter a list by using advanced criteria in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

↑ Back to the top

Keywords: KB214100, kbhowto

↑ Back to the top

Article Info
Article ID : 214100
Revision : 4
Created on : 1/24/2007
Published on : 1/24/2007
Exists online : False
Views : 410