XL: Custom AutoFilter Won't Filter Numbers Formatted as Text
ID: Q119257
|
The information in this article applies to:
-
Microsoft Excel for the Macintosh, versions 5.0, 5.0c
-
Microsoft Excel for Windows, versions 5.0, 5.0c
-
Microsoft Excel for Windows 95, version 7.0
SYMPTOMS
In Microsoft Excel, when you use the Custom AutoFilter dialog box to filter
number values that have been entered as text, and you select the <>
comparison operator, all of the numbers are displayed, even if they are
equal to the comparison value.
CAUSE
The <> comparison operator is designed to filter records that are not equal
to the value that you use with the comparison criteria. However, this
criteria does not work to filter number values that are formatted as text.
This behavior affects numbers that meet the following conditions:
- Numbers in an imported database file when the numbers are contained in a
column defined as a text field
- Numbers that are entered in cells that are previously formatted as text
- Numbers preceded by the apostrophe (') character
NOTE: When you select the = comparison operator, the numbers are correctly
filtered.
WORKAROUND
To work around this problem, use one of the following methods:
Method 1
Enter the numbers first, format the numbers as text, and then filter the
numbers.
NOTE: Use Method 3 if the data you want to filter contains a zero value
that is formatted as text, such as "00."
Method 2
If you format the cells as text prior to entering the numbers, or if you
import data from another program and the numbers are formatted as text,
use the following steps to convert the text format to the number format:
- In a blank cell on the worksheet that contains the imported data, type
the number 1.
- Select the 1, and then click Copy on the Edit menu.
- Select the range of imported numbers.
- On the Edit menu, click Paste Special.
- In the Paste Special dialog box, click Multiply, and then click OK.
NOTE: When you use this method, a zero value that is formatted as text,
such as "00" or "000" (without the quotation marks), is converted to the
number zero, "0" (without the quotation marks). Use Method 3 if the data
you are filtering contains a zero value that you do not want to convert to
the number zero.
Method 3
To filter records that are not equal to a number, use the <> comparison
operator and set the comparison criteria to the number followed by the * or
?. For example, to filter records that are not equal to the value 43, use
the comparison operator <> and the comparison criteria 43*.
The asterisk (*) wildcard character represents any series of characters,
while the question mark (?) represents any single character.
To use a wildcard character, follow these steps:
- Click in the column that contains the number values.
- On the Data menu, click Filter, and then click AutoFilter. (AutoFilter
should now have a check mark next to it.)
- Click the drop-down arrow in the first cell in the range of number
values that you want to filter, and click (Custom).
- In the Custom AutoFilter dialog box, in the list of comparison operators
(the first drop-down list), click <>.
- In the text box to the right of the first drop-down box, type the number
you are filtering, followed by an asterisk (*).
- Click OK.
MORE INFORMATION
When you filter zero values that are formatted as text, use the question
mark (?) wildcard to filter a specific zero value. For example, to search
for "00," type "0?" (without the quotation marks) in the text box. To
search for "000," type "0??" (without the quotation marks) in the text box.
Use the asterisk (*) wildcard to filter all other zero values except for
"0." For example, to search for "00," "000," "0000," and so on, type "0*"
(without the quotation marks) in the text box.
To exclude all zero values including "0", use the following steps:
- Click in the column that contains the number values.
- On the Data menu, click Filter, and then click AutoFilter. (AutoFilter
should now have a check mark next to it.)
- Click the arrow in the first column in the range of number values that
you want to filter, and click (Custom).
- In the Custom AutoFilter dialog box, in the list of comparison operators
(the first drop-down list), click <>.
- In the text box to the right of the first drop-down box, type "0*"
(without the quotation marks).
- Click the And option.
- Click the lower drop-down arrow, and click <>.
- In the text box to the right, type "0" (without the quotation marks).
- Click OK.
REFERENCES
For more information about the Custom AutoFilter Dialog Box, choose
the Search button in Help and type:
Custom AutoFilter dialog box
Additional query words:
5.00c auto filter
Keywords : kbualink97
Version : 5.00 5.00c 7.00
Platform : WINDOWS
Issue type :
|