Find a match of a word in a text string.

[Ed Denmark]

How would I write a formula that will isolate a
particular word in a text string.
i.e: the word CROWN

3/4 x 3-1/16 Red Oak Crown
3/4 x 3-1/8 Crown Hickory
Maple Crown 3/4 x 4-1/8
Crown, Walnut 1-1/16 x 7-1/4

These descriptions will be mixed in with 100's of other
codes that do not have "crown" in them. A true false
statement will also work for me.

Thanks in advance, Ed

 

[Harald Staff]

Hi Ed

A true/false approach:
=NOT(ISERR(FIND("crown",LOWER(A1))))

 

[craig]

The "SEARCH()" worksheet should help.
Syntax as follows:
=SEARCH("CROWN",A1)
Place the formula in a spare column and copy downwards to
the length of your table.
If the text string "CROWN" is not found in the cell being
interrogated, you will get an error returned. If the
string is found, you will get the character placing at
which it starts within the cell (bit of a bonus that you
probably won't need).
Type "search" into your help index for more info.

 

[Felipe]

=ISNUMBER(FIND("crown",A1))

Regards,
Felipe

 

[Anders S]

Another way,

=NOT(ISERROR(SEARCH("crown",B1)))

HTH
Anders Silven

 

[Harlan Grove]

How would I write a formula that will isolate a
particular word in a text string.

....

Another approach if the string being searched is in a cell like A1.

=(COUNTIF(A1,"*crown*")=1)