Excel: Emulating Wildcards in Numeric Database Functions

Last reviewed: November 2, 1994
Article ID: Q64350

SUMMARY

If a criteria is in the form of "text", Microsoft Excel will find or extract all records that contain "text" as a portion of the record. For example, "braun" and "brown" both meet the criteria of "br".

Wildcard characters can be used to find database entries that share some characters but not others. By using the question mark (?), Excel will find any single character in the position of the wildcard. By using the asterisk (*), Excel will find any number of characters in the position of the wildcard.

Wildcard characters do not apply to numbers, however.

The LEFT(), MID() and/or RIGHT() functions can be used in a computed criteria to emulate wildcards in database functions that use number entries.

For example, the following computed criteria can be used to perform database functions on all records that have an entry in the "ZIP Code" field starting with 995:

   +-------------------+
   | ZIPCompute        | <---- Does NOT match a field name. (Required
   +-------------------+       of computed criteria.)
   | =LEFT(C2,3)="995" | <---- LEFT() function: "C2" is first record
   +-------------------+       in "ZIP Code" field. Must be relative
                               reference.

Performing database functions using the above computed criteria will yield only those ZIP codes beginning with 995.

MORE INFORMATION

For more information on computed criteria, see pages 171-173 of the "Microsoft Excel Reference" version 2.20 manual or pages 144-145 of the "Microsoft Excel User's Guide" version 1.50 manual.

For more information in this database, query on the following words:

   computed and criteria

For more information on the LEFT(), MID() and RIGHT() functions, see pages 51-52, 59-60, and 75, respectively, of the "Microsoft Excel Functions and Macros" version 2.20 manual. If you are using version 1.50, see pages 69, 76-77, and 90, respectively, of the "Microsoft Excel Arrays, Functions, and Macros" version 1.50 manual.

KBCategory: kbother
KBSubcategory:
Additional reference words: noupd

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