search and extract the patterns

[ananpani]

Hi There,
can derive the output in multiple steps, but looking forward to find a formulae to do it one single step.. I tried using combinations of SEARCH,ISNUMBER functions, but did not get it right. So your help is highly appreciated.

Text	Desired Output	Search values
Ten oranges have been sold	oranges	oranges
Yet to sell 15 potatos	potato	bananas
oranges shipped	oranges	grapes
potatos under inspection	potatos	potatas
grapes are sweet	grapes
oranges to be picked up from farm	oranges
oranges on the way to customer	oranges
potatos are fresh from farm	potatos
potatos saved lives	potatos
potatos are the best of vegges	potatos
oranges are the best of fruits	oranges

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 

[ananpani]

Hi There,
can derive the output in multiple steps, but looking forward to find a formulae to do it one single step.. I tried using combinations of SEARCH,ISNUMBER functions, but did not get it right. So your help is highly appreciated.

Text	Desired Output	Search values
Ten oranges have been sold	oranges	oranges
Yet to sell 15 potatos	potato	bananas
oranges shipped	oranges	grapes
potatos under inspection	potatos	potatas
grapes are sweet	grapes
oranges to be picked up from farm	oranges
oranges on the way to customer	oranges
potatos are fresh from farm	potatos
potatos saved lives	potatos
potatos are the best of vegges	potatos
oranges are the best of fruits	oranges

<tbody>
</tbody>

I have tried to simplify the problem in the above example, but I have 100 search values and 1000s of text to look..
I am able to combine functions for these 4 search values, but there must be better way to do it, as I have 100s of search values. Kindly help
=IF(ISNUMBER(SEARCH(C$2,A2,1)),C$2,IF(ISNUMBER(SEARCH(C$3,A2,1)),C$3,IF(ISNUMBER(SEARCH(C$4,A2,1)),C$4,IF(ISNUMBER(SEARCH(C$5,A2,1)),C$5,0))))

 

[Aladin Akyurek]

In B2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$C$2:$C$5&" "," "&$A2&" "),$C$2:$C$5)

 

[ananpani]

Thanks Aladin, that solved the problem. cheers

 

[Aladin Akyurek]

Thanks Aladin, that solved the problem. cheers

You are welcome. Thanks for the update.