Excel: Creating Criteria to Find Numbers or Text in FieldLast reviewed: October 31, 1994Article ID: Q24666 |
SUMMARYTo extract records from a Microsoft Excel for the Macintosh version 1.0. 1.03, 1.04, 1.06, 1.5, 2.2, or 3.0 database based on whether a field in the record contains a number or text, use a computed criteria with the TYPE function. For example, to find all records that contain a number in the "CODE" field of the following database, a computed criteria has been created in E1:E2:
+---+-----------+----------+---+-------------+ | | A | B | D | E | +---+-----------+----------+---+-------------+ | 1 | NAME | CODE | | | +---+-----------+----------+---+-------------+ | 2 | Sam | 156 | | =TYPE(B2)=1 | +---+-----------+----------+---+-------------+ | 3 | Nancy | 34a-12 | | | +---+-----------+----------+---+-------------+ | 4 | Fred | 10021 | | | +---+-----------+----------+---+-------------+ | 5 | Carol | 7b-15 | | | +---+-----------+----------+---+-------------+To find all records that contain a text string in the "CODE" field of the database, you must enter the formula =TYPE(B2)=2 in cell E2 of the computed criteria. To define the criteria range, select cells E1:E2 and choose Set Criteria from the Data menu.
MORE INFORMATIONWhen creating a computed criteria, you must observe the following rules:
Note: This information also applies to Microsoft Excel for Windows versions 2.1, 2.1c, and 3.0, and to Microsoft Excel for OS/2 versions 2.2, 2.21, and 3.0.
|
KBCategory: kbother
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |