XL: Custom AutoFilter Won't Filter Numbers Formatted as Text

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

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:

1. In a blank cell on the worksheet that contains the imported data, type the number 1.

2. Select the 1, and then click Copy on the Edit menu.

3. Select the range of imported numbers.

4. On the Edit menu, click Paste Special.

5. In the Paste Special dialog box, click Multiply, and then click OK.

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:

1. Click in the column that contains the number values.

2. On the Data menu, click Filter, and then click AutoFilter. (AutoFilter should now have a check mark next to it.)

3. Click the drop-down arrow in the first cell in the range of number values that you want to filter, and click (Custom).

4. In the Custom AutoFilter dialog box, in the list of comparison operators (the first drop-down list), click <>.

5. In the text box to the right of the first drop-down box, type the number you are filtering, followed by an asterisk (*).

6. 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:

1. Click in the column that contains the number values.

2. On the Data menu, click Filter, and then click AutoFilter. (AutoFilter should now have a check mark next to it.)

3. Click the arrow in the first column in the range of number values that you want to filter, and click (Custom).

4. In the Custom AutoFilter dialog box, in the list of comparison operators (the first drop-down list), click <>.

5. In the text box to the right of the first drop-down box, type "0*" (without the quotation marks).

6. Click the And option.

7. Click the lower drop-down arrow, and click <>.

8. In the text box to the right, type "0" (without the quotation marks).

9. Click OK.

REFERENCES

For more information about the Custom AutoFilter Dialog Box, choose the Search button in Help and type:

   Custom AutoFilter dialog box