0

I'm cleaning a dirty directory. A few (just a thousand or so) records have their postcode messed up, either blank or containing other address data. Usually that means city or county.

A Postcode, whether UK, US, or Canada, has at least one digit in it. A city or county or blank field doesn't. So if I want to filter for just the bad-postcode rows, I want: All rows where postcode column has no numeric digits.

Filter allows 'doesn't contain' but only for specific strings, apparently not expressions like [0..9]. So how?

Improve this question
2
  • Those are text filters, not designed for numbers
    – Raystafarian
    Jul 10, 2015 at 16:07
  • You can try ISNUMBER if the bad (or good) rows will contain only a single number.
    – Byron Wall
    Jul 10, 2015 at 16:25

1 Answer 1

Reset to default
1

If you use a helper column you can filter on that

If your codes are in column C, you can put

=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},C2))>0

In another column and drag it down. All the TRUE has a number. Filter for only FALSE.

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.