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]
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]