Search for length without using blank cell with LEN()

[jpfulton]

I frequently have a column of zip codes in excel and need to do a quick check of them to make sure all are either 10 digits or 5 digits (those pesky 0 leading east coast zip codes frequently become 4 digits). I know that I can create a blank column and do =LEN(A1) then autofill then copy and paste values and then either sort that column to isolate the offenders or Ctrl+F through that column to find the offenders.

I'm looking for a way to simply select a column, press CTRL+F or equivalent and be able to find length right from that dialog box. Bonus points for boolean capability eg. search for NOT 5

Thank you!!

 

[Jonmo1]

Is it only the 4 digit ones you're trying to flag?

Try the AutoFilter, for Less Than 10000

 

[jpfulton]

Is it only the 4 digit ones you're trying to flag?

Try the AutoFilter, for Less Than 10000

Wow, yeah, that does work perfectly for the 4 digit zip codes. That's a cool quick solution.

If possible I'd like a more universal solution. My job involves spending a whole lot of time with Excel spreadsheets manipulating data. Most of the files have sloppy data entry and part of my job involves cleaning it up. I have many tricks and tools I've developed over the years I've been doing this. Having a length search method/tool would be an awesome addition to my arsenal.

Sometimes I'm checking phone numbers too I might get a list of 1000 records and at a quick glance it looks like the customer did a good job with clean data entry with maybe this format: (222) 222-2222 which is but then there are a couple (222)222-2222 in there or even some with a trailing space or with now dash. The list goes on and on.

P.S. I'm using Excel 2003. Not sure if there are features that have been added in later versions that could help. Willing to upgrade if needed.

 

[Jonmo1]

If you're using CTRL+F (Find)
You can use wildcards...

???? would find 4 digits
??????? would find 7 digits
etc..

Make sure to check the box "match entire cell contents"

 

[jpfulton]

Nailed it. That is absolutely perfect. Any chance it will let you do "NOT ?????" or something similar.

 

[Jonmo1]

This would get anything more than 5
??????*

Not sure about LESS, for that I'd do the autofilter method of less than 10000

 

[jpfulton]

Very cool. Thank you much. I had no idea there was such a simple solution. Made my day.

 

[Jonmo1]

You're welcome.