Text look up

[Niju David]

How do I pick up a particular word from a sentence in excel.
For eg:- I need to pick up the word "blue" form the sentence "My shirt is
blue in colour". I need a generic formula because there a number of sentences
and a number of colours.

 

[Bob Phillips]

What do you mean by pickup, get its start character?

=FIND(lookup_word_cell, sentence_cell)

--
HTH

Bob

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

 

[Bernard Liengme]

In C11 I have the colors: blue, green,red. You can add more as needed
In A2:A3 I have some phrases ,,, you can add more as needed
Mine are
my shirt in blue in color
trees have green leaves
he had a red book


In C2 I have =IF(LEN(SUBSTITUTE($A2,C$1,""))<>LEN($A2),C$1,"")
This is copied across under every color and down to 'catch' every phase
So now the colors appear in their own columns
In B2 I used =C2&D2&E2 and copied down the column
Now column B has the color that appears in the phase next to it.

Any help?

 

[Niju David]

Dear Bob
Thank you for your reply
The problem goes like this:
Column A contains a list of sentences which contains different colours names
in it.
ColumnE contains a list of colour names only
ColumnF contains list of names against these colours

I need a formula in column B such that if the first cell in column A
contains any of the colour in column E then it will show the name in column F

 

[Bob Phillips]

Something like this array formula?

=INDEX(B1:B5,(COUNTIF(A1:A100,"*"&T(OFFSET(B1,ROW(B1:B5)-1,0))&"*")))

--
HTH

Bob

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