Article ID: 170230
Article Last Modified on 1/20/2007
APPLIES TO
- Microsoft Office Excel 2003
- Microsoft Excel 2000 Standard Edition
- Microsoft Excel 97 Standard Edition
- Microsoft Excel 2001 for Mac
- Microsoft Excel 98 for Macintosh
This article was previously published under Q170230
SYMPTOMS
When you use comparison operators or wild card characters, and then use the AutoFilter command to filter numeric data in Microsoft Excel, you may receive incorrect results.
CAUSE
Comparisons that rely on character positions (beginning, ending, containing, and/or wild cards) do not work with numeric data in Microsoft Excel.
WORKAROUND
To use these operators or wild cards to filter numeric data, convert the data to text as in the following example.
Example
The following example assumes that the numeric data is as follows:
A1: 960632515 A2: 960632516 A3: 960632517 A4: 960632518 A5: 960632519
To convert the data to text, follow these steps:
- Select column B.
- On the Insert menu, click Columns.
In cell B1 (the first cell of the inserted column), enter the following formula:
=""&A1
- Select the range B1:B5.
- On the Edit menu, point to Fill, and then click Down.
The new column contains the text representations of the numbers in column A. - With the same range selected (B1:B5), click Copy on the Edit menu.
- Select cell A1.
- On the Edit menu, click Paste Special. Select Values and click OK.
- Select column B and click Delete on the Edit menu.
The numeric data that was in column A is in a text format and can be filtered using text filters.
To convert the data back to a numeric format, see the following article in the Microsoft Knowledge Base:
75945 How to convert text to numbers in Excel
STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.
MORE INFORMATION
When you use AutoFilter, wild card characters and some comparison operators work correctly with text only. The following filters rely on character positions and do not work with numeric data:
- begins with
- does not begin with
- ends with
- does not end with
- contains
- does not contain
- *
- ?
The asterisk (*) wild card character represents the position of any series of characters, while the question mark (?) represents the position of any single character.
Additional query words: XL XL2003 XL2000
Keywords: kbbug KB170230