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.

[TABLE="width: 490"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Text[/TD]
[TD]Desired Output[/TD]
[TD]Search values[/TD]
[/TR]
[TR]
[TD]Ten oranges have been sold[/TD]
[TD]oranges[/TD]
[TD]oranges[/TD]
[/TR]
[TR]
[TD]Yet to sell 15 potatos[/TD]
[TD]potato[/TD]
[TD]bananas[/TD]
[/TR]
[TR]
[TD]oranges shipped[/TD]
[TD]oranges[/TD]
[TD]grapes[/TD]
[/TR]
[TR]
[TD]potatos under inspection[/TD]
[TD]potatos[/TD]
[TD]potatas[/TD]
[/TR]
[TR]
[TD]grapes are sweet [/TD]
[TD]grapes[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]oranges to be picked up from farm[/TD]
[TD]oranges[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]oranges on the way to customer[/TD]
[TD]oranges[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]potatos are fresh from farm[/TD]
[TD]potatos[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]potatos saved lives[/TD]
[TD]potatos[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]potatos are the best of vegges[/TD]
[TD]potatos[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]oranges are the best of fruits[/TD]
[TD]oranges[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[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.

[TABLE="width: 490"]
<tbody>[TR]
[TD]Text[/TD]
[TD]Desired Output[/TD]
[TD]Search values[/TD]
[/TR]
[TR]
[TD]Ten oranges have been sold[/TD]
[TD]oranges[/TD]
[TD]oranges[/TD]
[/TR]
[TR]
[TD]Yet to sell 15 potatos[/TD]
[TD]potato[/TD]
[TD]bananas[/TD]
[/TR]
[TR]
[TD]oranges shipped[/TD]
[TD]oranges[/TD]
[TD]grapes[/TD]
[/TR]
[TR]
[TD]potatos under inspection[/TD]
[TD]potatos[/TD]
[TD]potatas[/TD]
[/TR]
[TR]
[TD]grapes are sweet[/TD]
[TD]grapes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]oranges to be picked up from farm[/TD]
[TD]oranges[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]oranges on the way to customer[/TD]
[TD]oranges[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]potatos are fresh from farm[/TD]
[TD]potatos[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]potatos saved lives[/TD]
[TD]potatos[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]potatos are the best of vegges[/TD]
[TD]potatos[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]oranges are the best of fruits[/TD]
[TD]oranges[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.