Solved

# Filter a column for any occurrence of a digit

Posted on 2014-08-19
188 Views
Hello, I would like to filter a column I'm excel.  The criteria would be any digit.  How can I do this?

Thank you
0
Question by:pdvsa

LVL 27

Accepted Solution

I don't know any filter/wildcard method that will filter on cells that contain whole numbers (0 - 9).  But you can do this with a helper column that will identify if the original cell contains any digits.

If your original data is in column A, add this array formula (using [Ctrl]+[Shift]+[Enter]) in an adjacent column and then copy it down as far as needed:
=OR(ISNUMBER(VALUE(MID(\$A2,ROW(\$A\$1:\$A\$100),1))))

You'll see TRUE or FALSE values:  TRUE means there is at least one digit in the original cell.

Regards,
-Glenn
0

Author Closing Comment

perfect!  thank you very much.
0

LVL 27

Expert Comment

You're welcome.  I'm going to hang onto this formula; it might prove useful in data checks.
0

## Featured Post

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.