Hi!

You can use the same formula in a helper column. Just make sure you enter
the formula as an array using the key combo of CTRL,SHIFT,ENTER. The formula
will return either TRUE or FALSE. TRUE meaning the name contains a fobidden
word. Then you can filter on that column.

Biff

"guy" wrote in message
...
really sorry for my poor presentation...
but still very appreciate your help!! thanks!

what i mean is that i have a list of "forbidden words" that cannot appear
on the student names.
for example, the list is {sex, jesus, coca cola, sony, fxxk, ...}
i want to identify the students whose names contain those "forbidden
words".
your suggestion has just given me a big help indeed, thanks! but if i also
want to filter the problem (formatted) records, that means to do something
like "auto filter". how can i achieve this?
or can i set any formula to highlight those records by a TRUE/FALSE value?

Many thanks again!!

"Biff"
l...
Hi!

So, what is it that you want to do? Identify student names that contain
the "forbidden" words?

Assume the keywords are on Sheet2 in the range A1:A5.

Select that range.
Goto InsertNameDefine
In the Names in Workbook box enter: Words
In the Refers to box enter: =Sheet2!$A$1:$A$5
OK out

Navigate to sheet1 and select the range of student names.
Assume that range is Sheet1 A1:A10
Goto FormatConditional Formatting
Select Formula Is
Enter this formula in the box:

=OR(ISNUMBER(SEARCH(Words,A1)))

Click the Format button
Select the Patterns tab
Select a color of your choice
OK out

Biff

"guy" wrote in message
...
I have more than 10000 student records in Excel worksheet 1. One of the
columns represent the student names. But there is a list of keywords that
is forbidden to appear in the name. The list is stored in worksheet 2.
How can I perform this task in Excel?

Many thanks!!