XL: Unexpected Results When Filtering Text Strings

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In Microsoft Excel, when you extract data from a list, you may receive unexpected results if your list contains numbers that are entered as text strings (by preceding the number with an apostrophe).

Numbers that are formatted as text strings and contain leading zeros may be extracted--this may not be the result you desire. For example, if your criteria is '012, the numbers '12, '012, 12, and '0012 all meet the criteria.

CAUSE

If the criteria is entered as a text string and not as part of the computed criteria, Microsoft Excel extracts all records that contain the text string at the beginning of the record even if that record is a numeric value.

RESOLUTION

To filter records in a list when the list contains an exact match to a numeric value entered as a text string, use computed criteria. For example, if you are working with data that resembles the following

   A1:  Number     B1:          C1:  Number
   A2:  '12        B2:          C2:  '012
   A3:  '012       B3:          C3:
   A4:  12         B4:          C4:
   A5:  '0012      B5:          C5:

   C1: ExactNumber
   C2: =A2="012"

REFERENCES

For more information about computed criteria, click the Search button in Help, and type:

   computed criteria