Microsoft KB Archive/134941

= Unexpected Results When Filtering Text Strings =

Article ID: 134941

Article Last Modified on 8/15/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q134941





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 want. 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: type the following information into cells C1:C2 of the Criteria Range in the example above:

C1: ExactNumber

C2: =A2="012"

NOTE: The criteria name in the first row of the criteria range (cell C1) can be any name except the name of a database field; if you use the name of a database field, Microsoft Excel interprets the criteria as comparison criteria. You can also leave the cell blank.



Excel 97
For more information about Advanced Filter criteria including computed criteria, click the Office Assistant, type advanced filter criteria, click Search, and then click to view "Examples of advanced filter criteria."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

120802 Office: How to Add/Remove a Single Office Program or Component

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

computed criteria

Click Show Topics, select the topic, and click Go To.

Additional query words: 5.00a 5.00c 8.00 97 XL97 98 XL98 filter autofilter database XL

Keywords: kbprb KB134941

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.