asked on

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?

[ fanpages ]

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)...

ASKER CERTIFIED SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byundt

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.

Subodh Tiwari (Neeraj)

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.

[ fanpages ]

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)