Excel: Extracting Numbers Entered as Text in a Database

SUMMARY

You must use computed criteria when referring to numbers that have been input into a Microsoft Excel database as text.

For example, if you have A1:A5 below defined as a database, defining B1:B2 as the criteria will locate the number "3" in cell A4.

                 +---+---------+------------+
                 |   |    A    |     B      |
                 +---+---------+------------+
                 | 1 | TextNum | ComputeNum |
                 +---+---------+------------+
                 | 2 |  ="1"   |  =A2="3"   |
                 +---+---------+------------+
                 | 3 |  ="2"   |            |
                 +---+---------+------------+
                 | 4 |  ="3"   |            |
                 +---+---------+------------+
                 | 5 |  ="4"   |            |
                 +---+---------+------------+

MORE INFORMATION

As with any computed criteria, the field name in the criteria must be different from any of the field names specified in the database. It is helpful to use a name that is related to the database field (in this case, ComputeNum).

For more information on setting computed criteria, see "Entering Computed Criteria" on pages 374-375 in "Microsoft Excel User's Guide" version 3.0 manual. If you are using Excel 2.20, see "Using Computed Criteria" on pages 171-173 of the "Microsoft Excel Reference" version 2.2 manual.

KBSubcategory: kbother KBSubcategory: