Excel Advanced Filter
 

I have filtered a table of numbers and when running advanced filter for items
"containing" the digits 1683, no rows appear. I know that there are scores of
entries containing this string. All cells are formatted as numbers. Any help,
please?

--
Nick

Excel Advanced Filter
 

What's your criteria

--


Regards,


Peo Sjoblom

"Nick M" wrote in message
...
I have filtered a table of numbers and when running advanced filter for
items
"containing" the digits 1683, no rows appear. I know that there are scores
of
entries containing this string. All cells are formatted as numbers. Any
help,
please?

--
Nick

Excel Advanced Filter
 

I selected the table for autofilter, and at the head of the part number
column I chose "Custom Autofilter". When the box appeared I chose "contains"
and entered a partial part number in the next box. Sometimes some of the par
numbers appear, sometimes nothing appears.

Does that make sense?
--
Nick


"Peo Sjoblom" wrote:

What's your criteria

--


Regards,


Peo Sjoblom

"Nick M" wrote in message
...
I have filtered a table of numbers and when running advanced filter for
items
"containing" the digits 1683, no rows appear. I know that there are scores
of
entries containing this string. All cells are formatted as numbers. Any
help,
please?

--
Nick

Excel Advanced Filter
 

So, you were using Autofilter, not the Advanced Filter which you mentioned
in the subject line.

The answer is that the "contains" tests are looking for text strings, not
for numbers.
--
David Biddulph

"Nick M" wrote in message
...
I selected the table for autofilter, and at the head of the part number
column I chose "Custom Autofilter". When the box appeared I chose
"contains"
and entered a partial part number in the next box. Sometimes some of the
par
numbers appear, sometimes nothing appears.

Does that make sense?
--
Nick


"Peo Sjoblom" wrote:

What's your criteria

--


Regards,


Peo Sjoblom

"Nick M" wrote in message
...
I have filtered a table of numbers and when running advanced filter for
items
"containing" the digits 1683, no rows appear. I know that there are
scores
of
entries containing this string. All cells are formatted as numbers. Any
help,
please?

--
Nick

Excel Advanced Filter
 

Your subject says advanced filter but you seem to use custom autofilter.
Advanced filter is another function.

If these are true numbers than you can't use contains which is a text
filter.
If they are text and numbers it should work.

If they are numbers then you can use a help column and a formula
assume they start in A2 going down, insert a new column next to column A
and in B2 put

=ISNUMBER(FIND(1683,A2))

copy down and apply filter on all your previous ranges and include the help
then column filter on TRUE

or extract them and filter on 1683

=MID(A2,FIND(1683,A2),4)




--


Regards,


Peo Sjoblom

"Nick M" wrote in message
...
I selected the table for autofilter, and at the head of the part number
column I chose "Custom Autofilter". When the box appeared I chose
"contains"
and entered a partial part number in the next box. Sometimes some of the
par
numbers appear, sometimes nothing appears.

Does that make sense?
--
Nick


"Peo Sjoblom" wrote:

What's your criteria

--


Regards,


Peo Sjoblom

"Nick M" wrote in message
...
I have filtered a table of numbers and when running advanced filter for
items
"containing" the digits 1683, no rows appear. I know that there are
scores
of
entries containing this string. All cells are formatted as numbers. Any
help,
please?

--
Nick

Excel Advanced Filter
 

It's been a long week and old men can get confused. Anyway, thanks for the
help. I'll study your recommendation and see if I can clear up this issue.
See my reply to David Biddulph as well.

Thanks for the help.

--
Nick


"Peo Sjoblom" wrote:

Your subject says advanced filter but you seem to use custom autofilter.
Advanced filter is another function.

If these are true numbers than you can't use contains which is a text
filter.
If they are text and numbers it should work.

If they are numbers then you can use a help column and a formula
assume they start in A2 going down, insert a new column next to column A
and in B2 put

=ISNUMBER(FIND(1683,A2))

copy down and apply filter on all your previous ranges and include the help
then column filter on TRUE

or extract them and filter on 1683

=MID(A2,FIND(1683,A2),4)




--


Regards,


Peo Sjoblom

"Nick M" wrote in message
...
I selected the table for autofilter, and at the head of the part number
column I chose "Custom Autofilter". When the box appeared I chose
"contains"
and entered a partial part number in the next box. Sometimes some of the
par
numbers appear, sometimes nothing appears.

Does that make sense?
--
Nick


"Peo Sjoblom" wrote:

What's your criteria

--


Regards,


Peo Sjoblom

"Nick M" wrote in message
...
I have filtered a table of numbers and when running advanced filter for
items
"containing" the digits 1683, no rows appear. I know that there are
scores
of
entries containing this string. All cells are formatted as numbers. Any
help,
please?

--
Nick

Excel Advanced Filter
 

Nick

you say 'containing' so a slight change to Peo's formula may (or may not)
help.

=ISNUMBER(SEARCH({8,6,3,1},A2)) will find a greater number in fact any
comination of 8,6,3,1 the following list will be returned as TRUE

1683, 8631, 6831, 3186, 3186, 15168324, 56813

Hope this helps.

Peter Atherton

"Nick M" wrote:

It's been a long week and old men can get confused. Anyway, thanks for the
help. I'll study your recommendation and see if I can clear up this issue.
See my reply to David Biddulph as well.

Thanks for the help.

--
Nick


"Peo Sjoblom" wrote:

Your subject says advanced filter but you seem to use custom autofilter.
Advanced filter is another function.

If these are true numbers than you can't use contains which is a text
filter.
If they are text and numbers it should work.

If they are numbers then you can use a help column and a formula
assume they start in A2 going down, insert a new column next to column A
and in B2 put

=ISNUMBER(FIND(1683,A2))

copy down and apply filter on all your previous ranges and include the help
then column filter on TRUE

or extract them and filter on 1683

=MID(A2,FIND(1683,A2),4)




--


Regards,


Peo Sjoblom

"Nick M" wrote in message
...
I selected the table for autofilter, and at the head of the part number
column I chose "Custom Autofilter". When the box appeared I chose
"contains"
and entered a partial part number in the next box. Sometimes some of the
par
numbers appear, sometimes nothing appears.

Does that make sense?
--
Nick


"Peo Sjoblom" wrote:

What's your criteria

--


Regards,


Peo Sjoblom

"Nick M" wrote in message
...
I have filtered a table of numbers and when running advanced filter for
items
"containing" the digits 1683, no rows appear. I know that there are
scores
of
entries containing this string. All cells are formatted as numbers. Any
help,
please?

--
Nick