| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to copy the first word or two words from a cell containing a complete sentence to another cell | Excel Discussion (Misc queries) | |||
| Swapping two words in a cell and placing them in another cell | Excel Worksheet Functions | |||
| counting occurrence of specific words in another group of words | Excel Programming | |||
| Calculate mean of test scores from rows of test answers | Excel Discussion (Misc queries) | |||
| How can i test a cell for bold style in a cell (Excel 2003)? | Excel Worksheet Functions | |||
Linear Mode
