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

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.

MORE INFORMATION

To use the Advanced Filter command to extract an exact match, follow these steps:

Start Microsoft Excel.

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

Select cells A5:C5.
On the Edit menu, click Copy.
Select cell A1.
On the Edit menu, click Paste.
In cell A2, type '=Roy.
Select cells A5:C11.
On the Data menu, point to Filter, and then click Advanced Filter.
Click the icon in the Criteria range box to shrink the Advanced Filter dialog box.
Select cells A1:A2.
Click the icon in the Criteria range box again to expand the Advanced Filter dialog box.
Click OK to sort on Roy.

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.