Filter for 225 or 233 numbers

  • Thread starter Thread starter hoganc
  • Start date Start date
H

hoganc

Still Stuck on this no. filter thing Im afraid

I'll give you some examples with this cod
=OR(LEFT(RIGHT(A2109,7),3)="225",LEFT(RIGHT(A2109,7),3)="233") i
shows up true


3052
01517334519
07969760557
01512333016 -- here 1
3018
3018
22528 --- here 2
02072330082


here 1 is fine but here 2 is not and so it significantly reduced m
sift but i will still have to do a manual check. Any ideas how to fi
this

the 1 and #VALUE code works also but again it needs to be counted fro
the right hand side. not the left as you can see its quite important

Any body any ideas how to fix this??? :
 
HoganC,

Solution 1)

Select your list, then use Data |
Filter... AutoFilter, and select Custom.

For the first row, select "Contains" in the left box, enter 233 in the box
to the right. Then select Or as the operator, and in the second row, select
"Contains" in the left box, and put 225 in the right box.

Then click OK and you're done - since it doesn't matter where in the string
the 233
is located.

Solution 2)

If you want a formula, use

=OR(NOT(ISERROR(FIND("225",A1))),NOT(ISERROR(FIND("233",A1))))

HTH,
Bernie
MS Excel MVP
 
Hi

if it needs to have four digits 225 or 222 after it then try this formula
=AND(LEN(A1)>=7,OR(LEFT(RIGHT(A1,7),3)="225",LEFT(RIGHT(A1,7),3)="233"))

Cheers
JulieD
 
Hi!

I think the data is so diverse it would stand a bit of simplification.
The following owes a bit to Eratosthenes, he of the sieve.
It can pretty well all be done in 1 formula but we've all fallen a
some hurdles in that (including the odd shifting hurdle ;)

Assuming data is in col A and cols B,C D are empty

First: you have to have 7 characters if you want nos 7,6 & 5 from th
end.

Filter out anything where Len(A1)<7 by using if(Len(A1)<7,"",A1) in B1
Copy down.
Now take the last 4 characters off the values in B by usin
=left(B1,Len(B1)-4)
in C1 and copy down.
Column C is very civilised by now and Autofilter using ends with 22
and ends with 233 will show the results.
But you can finish it off with this:
in D1 put =if(or(Right(C1,3)="225" ,Right((C1,3)="233"),1,""),
This will put 1 in for hits and leave misses blank.
Sorting on this final column will bubble the required items int
useable groups.

Al
 
Back
Top