Search word in column and display whole sentence

[kur5rt]

Dear friends,

I have following problem to be solve. I have to search word in the column and if found in the column then I have to display the whole scentance.

For example I have following text in my column

Book
The red pen is good
The brown pencil is big
telephone is ringing.

I want to search for word "red", if it found in column then I must display the complete sentance(The red pen is good) in another column by uisng excel formula. I do not want to use VBA.

Please any one can guide me how can i solve the above issue.

Thanks in advance.

With regards,

Anand Kumar

 

[SteveO59L]

=IF(ISERROR(SEARCH("red",A1)),"",A1)