Auto Filter

[lehigh]

Hi All:

I have a column numbers, all having 6 digits

I want to filter out only those numbers having a 7 in the third
position from the left.

Can someone please tell me how to set up the Custom Filter to do this?


Thanks for your help


Tom Snyder

 

[Andy Brown]

Can someone please tell me how to set up the Custom Filter to do this?

Number equals

??7*

Rgds,
Andy

 

[Debra Dalgleish]

You can use an Advanced filter to find numbers within a number. There
are instructions here:

http://www.contextures.com/xladvfilter01.html

In the criteria area, leave the heading cell blank, and in the cell
below, enter a formula that refers to the first row of data in the
column that you want to filter. For example:

=MID(J2,3,1)="7"

 

[Andy Brown]

Sorry, I missed "filter *out*".

Number *does not* equal ??7*

Rgds,
Andy

 

[Frank Kabel]

Hi Tom

I would add a helper column with a formula
=MID(A1,3,1)
copy down and filter for this column

Frank

 

[Peo Sjoblom]

Or use the advanced filter, assume the column is called MyRange
use Frank's formula in let's say C2 but add ="7"


=MID(A2,3,1)="7"

where A2 is the first cell with the numbers

do data>advanced filter, copy to another location,

$A$1:$A$200 (replace with the full range of your numbers plus a header)

Criteria range is

$C$1:$C$2

where C1 should be left empty