Advanced filter - multiple negative string match ORs???

[softwaresure]

Hi folks - been lurking here anonymously a while.

Gotta stumper I can't see solved anywhere else.

Trying to filter out any address line 1 values in this spreadsheet that DON'T begin with a street number (for obvious reasons - they're bogus/unusable)/

I tried adding a multi-row advanced filter criteria set to exclude any value that doesn't begin with 1, 2, 3, 4, 5, 6, 7 or 9:

<>1*
<>2*
<>3*

etc.

Weird results - one criteria (e.g. just the <>1* works fine).

Trying to use all 9 criteria does nothing.

Here's the file
data_test.xls

Help?

[jeffreybrown]

A quick way would be just add a helper column then use

=ISNUMBER(LEFT(I13,1)*1)

TRUE for a number, FALSE no

[softwaresure]

OK - forgive me for asking the next step, but I've spent 20 minutes googling "helper column + advanced filter" and similar terms, and I can't piece this together to make it work.

Awesome that this formula correctly evaluates the address line "begins with number" condition, but how does advanced filter work with this helper column?

Thank you!!!!

[softwaresure]

Nevermind!

1. Create the column
2. Drag it all the way down
3. Do regular filter on TRUE or FALSE

THANKS!

[jeffreybrown]

By a helper column I just mean, find a empty column and use it.

In this case, in S12 place a header, in S13 put in the formula and copy it down to the end of your data.

Now turn on the filters and then filter on S12 by either TRUE or FALSE

[jeffreybrown]

You're welcome…glad you have a workable solution and thanks for the feedback...

Please consider marking this thread as solved if this resolves your issue.

[benishiryo]

hi softwaresure, welcome to the forum. i think that was what Jeffrey was saying. anyway, here's a solution for Advanced Filter. i like to use a formula instead of multiple rows of criteria. to do that, place this formula in say cell T2:
=OR(ISERR(LEFT(I13)+0),LEFT(I13)={"0","8"})

ensure T1 has no header. then do the Advanced Filter:
List Range:
$A$12:$R$34

Criteria Range:
Donations!$T$1:$T$2

i noticed you miss out the number 8 & 0, so i followed suit. if it's supposed to be factored in, you can use Jeffrey's formula in T2 too. Do mark it as "Solved" if nothing else

[softwaresure]

Solved twice, indeed!

Thanks!!!!

[softwaresure]

One last question.

I want to INCLUDE addresses that begin with a "P.O. Box" as valid.

Can't quite get the formula to work for this advanced filter - any tips here?

=OR(ISERR(LEFT(I5)+0),LEFT(I5)={"0","8"},AND(LEFT(I5)="P",LEFT(I6)=".",LEFT(I7)="O",LEFT(I7)="."))