Microsoft Excel 2000- Autofilter working incorrectly

[dab332]

I am using Microsoft Excel 2000 and I seem to be having a problem with the autofilter. The column I am trying to autofilter is a list of wireless numbers in this format(814)598-3724. When I use the autofilter I have tried begins with (814) or contains (814) and each time it returns 0 items. I know for a fact that there are cells in that column that begin with and contain the (814).

What am I doing wrong? Seems like I have run into this problem before in other spreadsheets using the same version.

 

[Lilliabeth]

Are there really parentheses in the phone numbers or are they just appearing there due to formatting? (Check the formula bar)

Look for 814 without the ()'s

 

[dab332]

Lilliabeth,

To answer your question, the parentheses really do appear in the format. I tried the way you suggested by only doing a filter on contains 814 without the parentheses and it still returns zero items.

Thanks, any other suggestions?

 

[Lilliabeth]

I don't know what is going on, but this might be a workaround:

Look for

Equals *814*

 

[AnotherHiggins]

Single click on a cell that starts with (814), do the parentheses appear then in the formula bar (the long bar at the top with the "=" on the left)[sup][red]1[/red][/sup]?

If that doesn't make sense to you, try this:
In an empty cell, enter the formula
[COLOR=blue white]=isnumber(A2)[/color]
(replace A2 with one of the cells containing a phone number)[sup][red]2[/red][/sup].

[sup][red]1[/red][/sup]If you don't see the parentheses in the formula bar and/or [sup][red]2[/red][/sup]if the ISNUMBER formula returns TRUE then the parentheses aren't really a part of the cell, they are appearing in the cell as a result of the cell's formatting.

The reason I'm pushing this is because it will cause what you are describing (both with 'contains' and 'begins with').

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[dab332]

John,

The parentheses do not appear to be part of the cell. In the formula bar it says 8145983724. Formatted properly it then becomes the (814) 598-3724.
I have tried the search twice again.. the first time with begins with 814 and it came it returned a value of zero. The second time with contains 814 and it returned a value of zero.

Any more suggestions?

Dan

 

[SkipVought]

dab332,

There is a HUGE, HUMONGUS difference between NUMBERS and NUMERIC STRINGS. They don't react to sort or filter features as you expect.

If you have BOTH in the column --

NUMBERS (formatted to DISPLAY like (999) 999-9999) and
NUMERIC STRINGS,

the autofilter will ONLY display the STRING values. The NUMBERS seem invisible.

Skip,
[sub]
[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it![/sub]

 

[Zathras]

So, since they are numbers, the only way to get the auto-filter result you want is a Custom Auto Filter like this
[tt]
is greater than 8140000000
And
is less than or equal to 8149999999
[/tt]

 

[dab332]

Money shot!

Zathras, that solution solves the problem. It now will return values.

Thanks everyone for all the help.

I thought I was going to have to convert the cells into strings but this is a quick fix to the problem.

 

[AnotherHiggins]

You could create a 'helper column' with the formula
[COLOR=blue white]=TEXT(A2,"(000) 000-0000")[/color]
Then fill that formula down.

Now the Autofilter will work for that column as you were expecting, ie 'begins with (814)'.

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.