You'll need to have the raw data in a separate sheet or column from the filtered data. Here I'm assuming the raw data is in a sheet named rawData column A.
For the filtered dat you can use:
=FILTER(rawData!A:A, isError(search("example.com", rawData!A:A)))
Put this formula in cell A1 of a new sheet where you want the filtered data.
The follow formula will do just that; text-to-column:
A1=5,233,6,2,6,7,2,2,6,6
A2=SPLIT(A1;",")
And the next; text-to-row:
A1=5,233,6,2,6,7,2,2,6,6
A2=TRANSPOSE(SPLIT(A1;","))
UPDATE 03-02-2013
If you split the result of A1 and paste the values, it will give the same result as all the lines of code used in the OP's answer. I gave it a shot at it as well with Google Apps Script and this is what I created: text to column
function mySplit() {
var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cell = sh.getActiveCell().getValues()[0];
var sCell = cell[0].split(",");
var row = sh.getActiveCell().getRowIndex();
var col = sh.getActiveCell().getColumnIndex();
sh.getRange(row,col+1,1,sCell.length).setValues([sCell]);
}
I simple use the build-in split function to split the result and add it it to the sheet, nothing more and nothing less.
Best Answer
Solution
Place this formula in cell C2:
What you want is the set difference. This formula will give you all the unique values of column A that do not appear in column B.
Details
MATCH(search_criterion, lookup_array, [match_type])checks if an element is found in the given array. In this case it checks the element from column A in the entire array of B. If it is not found it returns NA.IF(ISNA(...), A2:A, "")returns the contents of cell from column A if it was not matched, or an empty string if it was.SORT(UNIQUE(...))is optional, but will filter the results in alphabetical order and remove any duplicates which is probably what you want.Finally ARRAYFORMULA returns the result as an array so that it populates the entire C column.
More information: