filter for an *

  • Thread starter Thread starter sandburn
  • Start date Start date
S

sandburn

I have data in a column that contains some astericks. I want to pull out the
data that does not contain astericks into another sheet. I have tried
entering the formula in everyway I can think of but without valid results.
Since the * is used to represent characters in the filter, can i still filter
for it.
 
One way to deliver this dynamically ..
.. want to pull out the data that does not contain asterisks into another sheet
Click to expand...
Assume your source data in Sheet1, running in A2 down
In another sheet,
Put in A2:
=IF(Sheet1!A2="","",IF(ISNUMBER(SEARCH("~*",Sheet1!A2)),"",ROW()))
In B2:
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A,ROWS($1:1))))
Copy A2:B2 down to cover the max expected extent of source data in Sheet1,
say down to B100. Minimize col A. Col B will return the required, all neatly
packed at the top. Success? eternalize it, hit YES below
 
Try, also, "Advanced Filter" with a criteria: ~*
After filtering you can copy/paste the visible cells to another location.
Micky
 
Back
Top