How to find certain words contained in cell

[mljohn]

I have a cell that contains a description of a product. (H2)
In that description sometimes there are the words "Black", "Cyan", "Magenta" or "Yellow". If the H2 cell contains one of these words I want to have the I2 cell show the word that was found.

(H2) "Yellow baseball cap" = (I2) Yellow

What is the formula for this?

 

[gsistek]

Is the descriptor always the first word? If so use: =LEFT(H2,FIND(" ",H2)-1)

 

[Matty]

Try:

=LOOKUP(9.99999999999999E+307,SEARCH({"Black","Cyan","Magenta","Yellow"},H2),{"Black","Cyan","Magenta","Yellow"})

In I2.

Matty

 

[T. Valko]

I have a cell that contains a description of a product. (H2)
In that description sometimes there are the words "Black", "Cyan", "Magenta" or "Yellow". If the H2 cell contains one of these words I want to have the I2 cell show the word that was found.

(H2) "Yellow baseball cap" = (I2) Yellow

What is the formula for this?

If the word may appear anywhere within the cell...

=LOOKUP(1E100,SEARCH({"Black";"Cyan";"Magenta";"Yellow"},H2),{"Black";"Cyan";"Magenta";"Yellow"})

 

[mljohn]

Great.

Two questions:
1. If the cell does not contain any of those words how do I leave the result blank?

2. In the formula what does the 1E100 (1E+100) mean?

 

[raj08536]

interesting formulas.....

 

[T. Valko]

Great.

Two questions:
1. If the cell does not contain any of those words how do I leave the result blank?

Try one of these...

If you're using Excel 2007 or later:

=IFERROR(LOOKUP(1E100,SEARCH({"Black";"Cyan";"Magenta";"Yellow"},H2),{"Black";"Cyan";"Magenta";"Yellow"}),"")

This one will work in any version of Excel:

=LOOKUP("zzzzz",CHOOSE({1,2},"",LOOKUP(1E100,SEARCH({"Black";"Cyan";"Magenta";"Yellow"},H2),{"Black";"Cyan";"Magenta";"Yellow"})))

2. In the formula what does the 1E100 (1E+100) mean?

1E100 is scientific notation for a very large number, 1 followed by 100 zeros.

In this application the SEARCH function will return the starting position of the lookup word within the cell (if found).

Since a cell can contain a maximum of 32,767 characters, the largest value that the SEARCH function can return is 32,767.

So, we need a lookup value that is greater than 32,767. We could use 32,768 but 1E100 is nice and compact and easy to remember.