# Excel conditional filter if digits are less or more than

I have huge list of numbers ( ID), and it must be 10 digits. I want a conditional filter to extract any cell contains less or more than 10 digits.
How?
###### Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IT Services ConsultantCommented:
You could introduce a "helper" column to calculate the length of each entry in the column, using =LEN(<cell>), then filter upon that for a fixed value of 10, or...

Simply set the Custom Filter to be greater than 999999999 (Nine 9s) & less than 10000000000 (1 + Ten 0s)...

IT Services ConsultantCommented:
Using a "helper" column...

Q_28712896.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Mechanical EngineerCommented:
Are leading zeros valid characters in the ID field?

If so, I assume that your data are Text rather than Number. To test for valid values using a helper column (like fanpages suggested) you could use a formula like:
=AND(LEN(A1)<>10,VALUE(A1)<>0)

The above formula will return TRUE for valid entries and FALSE otherwise. Note that the formula will reject 0000000000 as an invalid entry.

I suggest using an AutoFilter on the results of the suggested formula.
Excel & VBA ExpertCommented:
I think there is no need for a helper column if IDs are numbers not text.
It can be achieve directly on the ID column --> Filter --> Number Filters --> Custom Filters.

Remember the smallest 10 digit number is 1000000000 and largest 10 digit number is 9999999999.
So if you want to see only 10 digits number, use And criteria with greater than 1000000000 and less than 9999999999 and if you want to see the numbers which are either less than 10 digits or greater than 10 digits, use OR criteria with less than 1000000000 and greater than 9999999999.
IT Services ConsultantCommented:
Thanks for closing the question, a500505077.

It seems you opted for the "Helper" column approach.

(sktneer: Please see my very first comment in this thread)
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.