Find Color in the string using formula.

Hi all,

I have some string in a column e.g:-

The pen is blue.
Red eyes of devil.
Sky is blue.
White is my favorite color.


and in other column i have some color listed e.g:-

Red
Green
Blue
Black
Brown
White
Pink

now i want the color in the string based on the color listed in other column e.g:-

The pen is blue. blue
Red eyes of devil. Red
Sky is blue. Blue
White is my favorite color. White


only by using formula.

thanks in advance

Assuming sentences are starting in A2 and your colour table is in F2:F8, then try:

=LOOKUP(9.9999E+307,SEARCH($F$2:$F$8,A2),$F$2:$F$8 )

copied down

Thanks NBVC...

Can you explain what the 9.9999E+307 represents?

Hi

Have a look http://www.excelfox.com/forum/f23/nu...l-formula-437/