Advanced Filter by Cell Formatting

[CJ]

Hi groupies

Is it possible to use an Advanced Filter to filter cells by their formatting.

For example. If I have a list of customers, some from the States some from
Canada and some from Europe, I would like to be able to filter by the format
of the data in the PostalCode/ZipCode field.

Canadian Postal Codes are L9L 9L9 format, US being 99999-9999 etc.

Any ideas?

 

[andy62]

Whew, for a minute there I thought you wanted to filter cells by their
formatting. I don't think you could format by whether something is bold or
red font or 10 point.

But you really want to filter by some aspect of the data itself, the easiest
in this case being whether the postal code starts with a letter or number.

Somewhere outside of your data range, set up these four cells (no quotes
around the header cells):

"US Post"
=ISNUMBER(VALUE(LEFT(B7,1)))

"Canadian Post"
=NOT(ISNUMBER(VALUE(LEFT(B7,1))))

B7 in the two folrmulas is the first cell in your column of postal codes
(not the header), so adjust it to your actual first cell. Then go to Data >>
Filter >> Advanced Filter. Leave "Filter the list in place" checked, List
range is your column of postal codes (including the header) and Criteria
Range is two cells, either the US Post header and the formula under it or
Canadian Post and the formula under it. Leave "Unique Records Only"
unchecked, and press Okay. This will filter your list to show only US or
Canadian codes.

I think this would be better with Autofilter since you are not copying the
data to another location, but that's not what you said you wanted.

HTH