Excel Advanced Filter

[Nick M]

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?

 

[Peo Sjoblom]

What's your criteria

--


Regards,


Peo Sjoblom

 

[Nick M]

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?

 

[Peo Sjoblom]

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]

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.

 

[Billy Liddel]

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