Test if a Cell contains either of 12 different words
 

I'm trying to test if a cell contains any of 12 different words (note the
words may be embedded in a phrase). Is there an easier way than to write 12
if statements?

Thanks.

Test if a Cell contains either of 12 different words
 

Hi Joy

Try the Select Case statement

Not sure how to test with embedded text though - something for me to play
with and find out

HTH

"J@Y" wrote:

I'm trying to test if a cell contains any of 12 different words (note the
words may be embedded in a phrase). Is there an easier way than to write 12
if statements?

Thanks.

Test if a Cell contains either of 12 different words
 

The embedded text is what I'm having trouble with ... I tried using "*text*"
with Select Case and it doesnt work, the * are recognized literally as *.

"steve_doc" wrote:

Hi Joy

Try the Select Case statement

Not sure how to test with embedded text though - something for me to play
with and find out

HTH

"J@Y" wrote:

I'm trying to test if a cell contains any of 12 different words (note the
words may be embedded in a phrase). Is there an easier way than to write 12
if statements?

Thanks.

Test if a Cell contains either of 12 different words
 

if instr(1,cell.value,"abc",vbTextcompare) 0 then
' contained in cell

--
Regards,
Tom Ogilvy



"J@Y" wrote:

The embedded text is what I'm having trouble with ... I tried using "*text*"
with Select Case and it doesnt work, the * are recognized literally as *.

"steve_doc" wrote:

Hi Joy

Try the Select Case statement

Not sure how to test with embedded text though - something for me to play
with and find out

HTH

"J@Y" wrote:

I'm trying to test if a cell contains any of 12 different words (note the
words may be embedded in a phrase). Is there an easier way than to write 12
if statements?

Thanks.

Test if a Cell contains either of 12 different words
 

In a worksheet, you can use something like:

=SUM(COUNTIF(B2,{"*APPLE*","*BUT*","*CAR*","*DAM*" ,"*EXCEL*","*FOR*","*GO*","*HI*","*AND*","*THE*"," *DOG*","*OR*"}))

this could be adapted to code as well.

Demo'd from the immediate window:

?
Evaluate("SUM(COUNTIF(B2,{""*APPLE*"",""*BUT*"","" *CAR*"",""*DAM*"",""*EXCEL*"",""*FOR*"",""*GO*""," "*HI*"",""*AND*"",""*THE*"",""*DOG*"",""*OR*""}))" )
4
? Range("B2").Value
Go for an apple


--
Regards,
Tom Ogilvy


"J@Y" wrote:

The embedded text is what I'm having trouble with ... I tried using "*text*"
with Select Case and it doesnt work, the * are recognized literally as *.

"steve_doc" wrote:

Hi Joy

Try the Select Case statement

Not sure how to test with embedded text though - something for me to play
with and find out

HTH

"J@Y" wrote:

I'm trying to test if a cell contains any of 12 different words (note the
words may be embedded in a phrase). Is there an easier way than to write 12
if statements?

Thanks.

Test if a Cell contains either of 12 different words
 

What exactly does the evaluate do?

"Tom Ogilvy" wrote:

In a worksheet, you can use something like:

=SUM(COUNTIF(B2,{"*APPLE*","*BUT*","*CAR*","*DAM*" ,"*EXCEL*","*FOR*","*GO*","*HI*","*AND*","*THE*"," *DOG*","*OR*"}))

this could be adapted to code as well.

Demo'd from the immediate window:

?
Evaluate("SUM(COUNTIF(B2,{""*APPLE*"",""*BUT*"","" *CAR*"",""*DAM*"",""*EXCEL*"",""*FOR*"",""*GO*""," "*HI*"",""*AND*"",""*THE*"",""*DOG*"",""*OR*""}))" )
4
? Range("B2").Value
Go for an apple


--
Regards,
Tom Ogilvy


"J@Y" wrote:

The embedded text is what I'm having trouble with ... I tried using "*text*"
with Select Case and it doesnt work, the * are recognized literally as *.

"steve_doc" wrote:

Hi Joy

Try the Select Case statement

Not sure how to test with embedded text though - something for me to play
with and find out

HTH

"J@Y" wrote:

I'm trying to test if a cell contains any of 12 different words (note the
words may be embedded in a phrase). Is there an easier way than to write 12
if statements?

Thanks.

Test if a Cell contains either of 12 different words
 

It evaluates the string passed to it as if it was entered in a cell in the
worksheet.

--
Regards,
Tom Ogilvy


"J@Y" wrote:

What exactly does the evaluate do?

"Tom Ogilvy" wrote:

In a worksheet, you can use something like:

=SUM(COUNTIF(B2,{"*APPLE*","*BUT*","*CAR*","*DAM*" ,"*EXCEL*","*FOR*","*GO*","*HI*","*AND*","*THE*"," *DOG*","*OR*"}))

this could be adapted to code as well.

Demo'd from the immediate window:

?
Evaluate("SUM(COUNTIF(B2,{""*APPLE*"",""*BUT*"","" *CAR*"",""*DAM*"",""*EXCEL*"",""*FOR*"",""*GO*""," "*HI*"",""*AND*"",""*THE*"",""*DOG*"",""*OR*""}))" )
4
? Range("B2").Value
Go for an apple


--
Regards,
Tom Ogilvy


"J@Y" wrote:

The embedded text is what I'm having trouble with ... I tried using "*text*"
with Select Case and it doesnt work, the * are recognized literally as *.

"steve_doc" wrote:

Hi Joy

Try the Select Case statement

Not sure how to test with embedded text though - something for me to play
with and find out

HTH

"J@Y" wrote:

I'm trying to test if a cell contains any of 12 different words (note the
words may be embedded in a phrase). Is there an easier way than to write 12
if statements?

Thanks.

Test if a Cell contains either of 12 different words
 

I see. Also, why do you use ""*Car*"" with double quotes instead of "*Car*"
which works normally for Countif statements?

"Tom Ogilvy" wrote:

It evaluates the string passed to it as if it was entered in a cell in the
worksheet.

--
Regards,
Tom Ogilvy


"J@Y" wrote:

What exactly does the evaluate do?

"Tom Ogilvy" wrote:

In a worksheet, you can use something like:

=SUM(COUNTIF(B2,{"*APPLE*","*BUT*","*CAR*","*DAM*" ,"*EXCEL*","*FOR*","*GO*","*HI*","*AND*","*THE*"," *DOG*","*OR*"}))

this could be adapted to code as well.

Demo'd from the immediate window:

?
Evaluate("SUM(COUNTIF(B2,{""*APPLE*"",""*BUT*"","" *CAR*"",""*DAM*"",""*EXCEL*"",""*FOR*"",""*GO*""," "*HI*"",""*AND*"",""*THE*"",""*DOG*"",""*OR*""}))" )
4
? Range("B2").Value
Go for an apple


--
Regards,
Tom Ogilvy


"J@Y" wrote:

The embedded text is what I'm having trouble with ... I tried using "*text*"
with Select Case and it doesnt work, the * are recognized literally as *.

"steve_doc" wrote:

Hi Joy

Try the Select Case statement

Not sure how to test with embedded text though - something for me to play
with and find out

HTH

"J@Y" wrote:

I'm trying to test if a cell contains any of 12 different words (note the
words may be embedded in a phrase). Is there an easier way than to write 12
if statements?

Thanks.

Test if a Cell contains either of 12 different words
 

double quotes embedded in a string must be doubled to be interpreted as a
double quote character and not the string delimiter.

--
Regards,
Tom Ogilvy


"J@Y" wrote:

I see. Also, why do you use ""*Car*"" with double quotes instead of "*Car*"
which works normally for Countif statements?

"Tom Ogilvy" wrote:

It evaluates the string passed to it as if it was entered in a cell in the
worksheet.

--
Regards,
Tom Ogilvy


"J@Y" wrote:

What exactly does the evaluate do?

"Tom Ogilvy" wrote:

In a worksheet, you can use something like:

=SUM(COUNTIF(B2,{"*APPLE*","*BUT*","*CAR*","*DAM*" ,"*EXCEL*","*FOR*","*GO*","*HI*","*AND*","*THE*"," *DOG*","*OR*"}))

this could be adapted to code as well.

Demo'd from the immediate window:

?
Evaluate("SUM(COUNTIF(B2,{""*APPLE*"",""*BUT*"","" *CAR*"",""*DAM*"",""*EXCEL*"",""*FOR*"",""*GO*""," "*HI*"",""*AND*"",""*THE*"",""*DOG*"",""*OR*""}))" )
4
? Range("B2").Value
Go for an apple


--
Regards,
Tom Ogilvy


"J@Y" wrote:

The embedded text is what I'm having trouble with ... I tried using "*text*"
with Select Case and it doesnt work, the * are recognized literally as *.

"steve_doc" wrote:

Hi Joy

Try the Select Case statement

Not sure how to test with embedded text though - something for me to play
with and find out

HTH

"J@Y" wrote:

I'm trying to test if a cell contains any of 12 different words (note the
words may be embedded in a phrase). Is there an easier way than to write 12
if statements?

Thanks.

Test if a Cell contains either of 12 different words
 

I see. Thanks so much for the solution. One more quick question, since
Evaluate passes the string as if it was entered in the cell, how would I
refer to the cell (in this case B2) in the Countif statement with a cell
variable?

"Tom Ogilvy" wrote:

double quotes embedded in a string must be doubled to be interpreted as a
double quote character and not the string delimiter.

--
Regards,
Tom Ogilvy


"J@Y" wrote:

I see. Also, why do you use ""*Car*"" with double quotes instead of "*Car*"
which works normally for Countif statements?

"Tom Ogilvy" wrote:

It evaluates the string passed to it as if it was entered in a cell in the
worksheet.

--
Regards,
Tom Ogilvy


"J@Y" wrote:

What exactly does the evaluate do?

"Tom Ogilvy" wrote:

In a worksheet, you can use something like:

=SUM(COUNTIF(B2,{"*APPLE*","*BUT*","*CAR*","*DAM*" ,"*EXCEL*","*FOR*","*GO*","*HI*","*AND*","*THE*"," *DOG*","*OR*"}))

this could be adapted to code as well.

Demo'd from the immediate window:

?
Evaluate("SUM(COUNTIF(B2,{""*APPLE*"",""*BUT*"","" *CAR*"",""*DAM*"",""*EXCEL*"",""*FOR*"",""*GO*""," "*HI*"",""*AND*"",""*THE*"",""*DOG*"",""*OR*""}))" )
4
? Range("B2").Value
Go for an apple


--
Regards,
Tom Ogilvy


"J@Y" wrote:

The embedded text is what I'm having trouble with ... I tried using "*text*"
with Select Case and it doesnt work, the * are recognized literally as *.

"steve_doc" wrote:

Hi Joy

Try the Select Case statement

Not sure how to test with embedded text though - something for me to play
with and find out

HTH

"J@Y" wrote:

I'm trying to test if a cell contains any of 12 different words (note the
words may be embedded in a phrase). Is there an easier way than to write 12
if statements?

Thanks.

Test if a Cell contains either of 12 different words
 

Because the quotes are within a quotes enclosed string, so you need to stop
them acting as ending and closing quotes. Doubling does that.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"J@Y" wrote in message
...
I see. Also, why do you use ""*Car*"" with double quotes instead of "*Car*"
which works normally for Countif statements?

"Tom Ogilvy" wrote:

It evaluates the string passed to it as if it was entered in a cell in
the
worksheet.

--
Regards,
Tom Ogilvy


"J@Y" wrote:

What exactly does the evaluate do?

"Tom Ogilvy" wrote:

In a worksheet, you can use something like:

=SUM(COUNTIF(B2,{"*APPLE*","*BUT*","*CAR*","*DAM*" ,"*EXCEL*","*FOR*","*GO*","*HI*","*AND*","*THE*"," *DOG*","*OR*"}))

this could be adapted to code as well.

Demo'd from the immediate window:

?
Evaluate("SUM(COUNTIF(B2,{""*APPLE*"",""*BUT*"","" *CAR*"",""*DAM*"",""*EXCEL*"",""*FOR*"",""*GO*""," "*HI*"",""*AND*"",""*THE*"",""*DOG*"",""*OR*""}))" )
4
? Range("B2").Value
Go for an apple


--
Regards,
Tom Ogilvy


"J@Y" wrote:

The embedded text is what I'm having trouble with ... I tried using
"*text*"
with Select Case and it doesnt work, the * are recognized literally
as *.

"steve_doc" wrote:

Hi Joy

Try the Select Case statement

Not sure how to test with embedded text though - something for me
to play
with and find out

HTH

"J@Y" wrote:

I'm trying to test if a cell contains any of 12 different words
(note the
words may be embedded in a phrase). Is there an easier way than
to write 12
if statements?

Thanks.

Test if a Cell contains either of 12 different words
 

Evaluate("SUM(COUNTIF(" & ActiveCell.Address &
",{""*APPLE*"",""*BUT*"",""*CAR*"",""*DAM*"",""*EX CEL*"",
""*FOR*"",""*GO*"",""*HI*"",""*AND*"",""*THE*"","" *DOG*"",""*OR*""}))")

--
Regards,
Tom Ogilvy

"J@Y" wrote in message
...
I see. Thanks so much for the solution. One more quick question, since
Evaluate passes the string as if it was entered in the cell, how would I
refer to the cell (in this case B2) in the Countif statement with a cell
variable?

"Tom Ogilvy" wrote:

double quotes embedded in a string must be doubled to be interpreted as a
double quote character and not the string delimiter.

--
Regards,
Tom Ogilvy


"J@Y" wrote:

I see. Also, why do you use ""*Car*"" with double quotes instead of
"*Car*"
which works normally for Countif statements?

"Tom Ogilvy" wrote:

It evaluates the string passed to it as if it was entered in a cell
in the
worksheet.

--
Regards,
Tom Ogilvy


"J@Y" wrote:

What exactly does the evaluate do?

"Tom Ogilvy" wrote:

In a worksheet, you can use something like:

=SUM(COUNTIF(B2,{"*APPLE*","*BUT*","*CAR*","*DAM*" ,"*EXCEL*","*FOR*","*GO*","*HI*","*AND*","*THE*"," *DOG*","*OR*"}))

this could be adapted to code as well.

Demo'd from the immediate window:

?
Evaluate("SUM(COUNTIF(B2,{""*APPLE*"",""*BUT*"","" *CAR*"",""*DAM*"",""*EXCEL*"",""*FOR*"",""*GO*""," "*HI*"",""*AND*"",""*THE*"",""*DOG*"",""*OR*""}))" )
4
? Range("B2").Value
Go for an apple


--
Regards,
Tom Ogilvy


"J@Y" wrote:

The embedded text is what I'm having trouble with ... I tried
using "*text*"
with Select Case and it doesnt work, the * are recognized
literally as *.

"steve_doc" wrote:

Hi Joy

Try the Select Case statement

Not sure how to test with embedded text though - something
for me to play
with and find out

HTH

"J@Y" wrote:

I'm trying to test if a cell contains any of 12 different
words (note the
words may be embedded in a phrase). Is there an easier way
than to write 12
if statements?

Thanks.