Filter not working as expected

[dkintheuk]

Hi all,

I'm using a spreadsheet with 31257 rows (including header row) that
has an auto filter applied.

One of the columns contains phone numbers and i want to use "Begins
with" to filter on the calls that begin with a particular few digits.

I get no results returned even if I use "Begins with" for just the
first digit of the phone number which is that same in all cases.

I've cleared the data and started with a fresh clean sheet, the data
is selected by highlighting the columsn then selecting Auto Filter.

In the drop down for the auto filter, I can see the different numbers
in the list and selecting one this way results in the correct rows
appearing, it is just when I try to use begins with.

Anyone got any good ideas? I'm using Excel 2003 in case you wondered.

Cheers,

Rob.

 

[Pete_UK]

Begins With works with text values, but you may have your phone
numbers entered as proper numbers, and so this will not work with
them. Phone numbers should be treated as text values (you wouldn't
normally expect to do arithmetic on them, would you?), and should
appear like 01612398191. Then you would be able to apply a custom
filter based on Begins With - in this case begins with 0161 would get
you the Manchester numbers.

Hope this helps.

Pete

 

[dkintheuk]

I think that might be it...

Of course because the numbers don't start with zeros I missed this
point. Yep the data is not text is it pure number!

And looking at it now I can make it owrk through another route and
that is to use left on the field to get the digits i want for
filtering and filter on that column instead - which is actually much
easier.

But thanks you've explained that really obvious thing I missed!

Cheers,

Rob.

 

[Pete_UK]

You're welcome, Rob - thanks for feeding back.

Pete