XL: Unexpected Results When Using AutoFilter with Numeric Data

Last reviewed: February 2, 1998
Article ID: Q170230

The information in this article applies to:

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

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:

   ARTICLE-ID: Q75945
   TITLE     : Converting 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. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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:
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,
Platform : MACINTOSH WINDOWS
Issue type : kbbug
Solution Type : kbworkaround

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.