Keep text filter away numbers

K

KH_GS

Hi

What formula would be good for filtering away cells that contain onl
numbers?

Also
Autofilter does custom take functions?
Advance filter does criteria take functions in the same manner
 
M

Max

KH_GS said:
What formula would be good for filtering away cells
that contain only numbers?
Click to expand...

One way ..

Assume source data in col A, from row1 down

Put in B1:
=IF(ISERROR(SMALL(C:C,ROW(A1))),"",
INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Put in C1: =IF(A1="","",IF(ISNUMBER(A1),"",ROW()))

Select B1:C1, fill down until the last row of data in col A

Col B should return the required results,
all neatly bunched at the top

And if there's the possibility of "text" numbers within col A
and these are to be treated as numbers (i.e. to be omitted)
then we could put instead in C1:
=IF(A1="","",IF(ISNUMBER(A1+0),"",ROW()))

(Formula in col B unchanged)
 
Top