Using fields within string functions?

[alpo]

First, let me explain what I am trying to do.

I have a field, a1:a1000, of email address usernames (e.g. the par
before the "@" symbol). I have another field, b1:b100, which is lis
of profanities, racial slurs, and all other kinds of bad words. What
need to do is flag each username that contains any of the bad words.
The flags can be placed in column C or whatever.

The SEARCH function works great if I'm only comparing two cells. But
I need to compare one cell with a field of cells.

COUNTIF will compare one cell to a field of cells, but it has to be a
exact match. It will not match "apple" to "sjfkapple234", fo
example.

I tried using a field in the SEARCH function and it doesn't throw a
error, but it also does not increment..it only looks at the first cel
in the field.

The only way I have been able to get this to work is to compare ever
cell individually with tons and tons of SEARCH functions, then tall
them up. In this case, it would take 100,000 cells (100 x 1000) to d
this, which is ridiculous. There has to be a simpler way

 

[alpo]

If it matters, I'm using Excel 2000. 9.0.2720

 

[Jason Morin]

You can put this in C1 and fill down:

=IF(SUMPRODUCT(COUNTIF(A1,"*"&$B$1:$B$100&"*")),"Bad Word
Found","")

HTH
Jason
Atlanta, GA

 

[Jim Rech]

COUNTIF will compare one cell to a field of cells, but it has to be an
exact match. It will not match "apple" to "sjfkapple234", for
example.

Try using wildcards:

=COUNTIF($A$1:$A$1000,"*"&B1&"*")


--
Jim Rech
Excel MVP
| First, let me explain what I am trying to do.
|
| I have a field, a1:a1000, of email address usernames (e.g. the part
| before the "@" symbol). I have another field, b1:b100, which is list
| of profanities, racial slurs, and all other kinds of bad words. What I
| need to do is flag each username that contains any of the bad words.
| The flags can be placed in column C or whatever.
|
| The SEARCH function works great if I'm only comparing two cells. But,
| I need to compare one cell with a field of cells.
|
| COUNTIF will compare one cell to a field of cells, but it has to be an
| exact match. It will not match "apple" to "sjfkapple234", for
| example.
|
| I tried using a field in the SEARCH function and it doesn't throw an
| error, but it also does not increment..it only looks at the first cell
| in the field.
|
| The only way I have been able to get this to work is to compare every
| cell individually with tons and tons of SEARCH functions, then tally
| them up. In this case, it would take 100,000 cells (100 x 1000) to do
| this, which is ridiculous. There has to be a simpler way.
|
|
| ---
| Message posted
|

 

[alpo]

Both of those worked. I've just started to learn string manipulation.
I really appreciate your help. I've been trying to get this to wor
for like 2 hours..lol