Excel: Data Extract with Wildcards on Nontext Items

Last reviewed: November 30, 1994
Article ID: Q41624

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
Microsoft Excel for OS/2, versions 2.2, 3.0
Microsoft Excel for the Macintosh versions 2.2, 3.0, 4.0, 5.0

SUMMARY

Microsoft Excel supports the use of wildcards such as "*" and "?" in a database criteria only when searching a column of text. A column of values will not extract properly with wildcards in the criteria. Once you use a wildcard in a criteria, the criteria is treated as text; comparing a text criteria to a value will never be equal.

MORE INFORMATION

The following is an example of a method to simulate the use of wildcards to search a column of values:

   A1:   Name    B1: Zip
   A2:   Joe     B2: 98121
   A3:   Jim     B3: 98345
   A4:   Mary    B4: 98223
   A5:   John    B5: 12345

When extracting all of the people that had names that started with "J", the criteria would look like the following:

   D1: Name
   D2: J*

Choosing Extract from the Data menu would then extract a list that looks like the following:

   Name
   Joe
   Jim
   John

To do the same thing with the zip code, such as extract every zip code that starts with "98", the criteria would be:

   D1:
   D2: =LEFT(TEXT(B2,"##"),2)="98"

Note the blank cell, D1. Using a blank as the column title for the criteria tells Excel that you are using a computed criteria.

Using the above criteria and then choosing Extract from the Data menu would extract a list that looks like the following:

In Microsoft Excel version 5.0, you can filter your data more easily than performing an extract. To filter your information, choose Filter from the Data menu, then choose Advanced Filter and enter the criteria.

REFERENCES

"Microsoft Excel User's Guide," version 3.0, page 374

"Microsoft Excel Reference," version 2.x, page 180

KBCategory: kbusage
KBSubcategory:
Additional words: 2.00 2.0 2.01 2.1 2.10 2.2 2.21 2.20 3.00
3.0 4.00 4.0 wild card

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: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.