Text function

[Sid_2987]

I'm having a group of keywords which I want to match with in particular text.

Below is the example.

[TABLE="width: 359"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 487"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]List[/TD]
[TD]Item[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]I love apple[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD]apple[/TD]
[/TR]
[TR]
[TD]Apple is sweet fruit[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD]banana[/TD]
[/TR]
[TR]
[TD]Banana is rich in carbohydrate[/TD]
[TD]banana[/TD]
[TD][/TD]
[TD]watermelon[/TD]
[/TR]
[TR]
[TD]apple / banana comes in fruit category[/TD]
[TD]banana[/TD]
[TD][/TD]
[TD]Cherry[/TD]
[/TR]
[TR]
[TD]banana / watermelon is my favorite[/TD]
[TD="colspan: 2"]watermelon[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]watermelon / cherry / apple is nice for health[/TD]
[TD]Cherry[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here I am writing my function to get the matching result. "=LOOKUP(2^15,SEARCH(fruit,A3),fruit)" where fruits : apple, banana, watermelon & cherry.

Question: Is there anyway to write a function so that I can get the first fruit name appearing in list (Bold fruit name)?

 

[Jonmo1]

Try

=INDEX(fruit,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(fruit,A3)),0),0))

 

[Sid_2987]

Hi Thanks Jonmo1,

I tried with your formula but is not working. SEARCH function taking keyword array based on the order.

[TABLE="width: 521"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]List [/TD]
[TD]Item[/TD]
[TD][/TD]
[TD]Fruit1[/TD]
[/TR]
[TR]
[TD]I love apple[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD]watermelon[/TD]
[/TR]
[TR]
[TD]Apple is sweet fruit[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD]cherry[/TD]
[/TR]
[TR]
[TD]Banana is rich in carbohydrate[/TD]
[TD]banana[/TD]
[TD][/TD]
[TD]banana[/TD]
[/TR]
[TR]
[TD]apple / banana comes in fruit category[/TD]
[TD]banana[/TD]
[TD][/TD]
[TD]apple[/TD]
[/TR]
[TR]
[TD]banana / watermelon is my favorite[/TD]
[TD="colspan: 2"]watermelon[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]watermelon / cherry / apple is nice for health[/TD]
[TD="colspan: 2"]watermelon[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]watermelon [/TD]
[TD="colspan: 2"]watermelon[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]watermelon / apple[/TD]
[TD="colspan: 2"]watermelon[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Can you try it once?

 

[Jonmo1]

You said

Is there anyway to write a function so that I can get the first fruit name appearing in list (Bold fruit name)?

I took that to mean you wanted the one that appears first in the range named 'fruit'

Given below example,
A5 has both apple and banana in the cell.
But banana appears first in the fruit list, so the formula returns banana.

A6 has both banana and watermelon in the cell.
But watermelon appears first in the fruit list, so the formula returns watermelon.

Unknown
	A	B	C	D
1	List	Item		Fruit1
2	I love apple	apple		watermelon
3	Apple is sweet fruit	apple		cherry
4	Banana is rich in carbohydrate	banana		banana
5	apple / banana comes in fruit category	banana		apple
6	banana / watermelon is my favorite	watermelon
7	watermelon / cherry / apple is nice for health	watermelon
8	watermelon	watermelon
9	watermelon / apple	watermelon
Sheet1

Cell Formulas
Range	Formula
B2	=INDEX(fruit,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(fruit,A2)),0),0))
B3	=INDEX(fruit,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(fruit,A3)),0),0))
B4	=INDEX(fruit,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(fruit,A4)),0),0))
B5	=INDEX(fruit,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(fruit,A5)),0),0))
B6	=INDEX(fruit,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(fruit,A6)),0),0))
B7	=INDEX(fruit,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(fruit,A7)),0),0))
B8	=INDEX(fruit,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(fruit,A8)),0),0))
B9	=INDEX(fruit,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(fruit,A9)),0),0))

Named Ranges
Name	Refers To	Cells
fruit	=Sheet1!$D$2:$D$5

 

[Sid_2987]

Hi Jonmo1,

Basically I mentioned List as attribute. Marked as in Bold Letter. Also I have Item (as output of formula). So from List attribute I want bold letter into Item column.

[TABLE="width: 521"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]List[/TD]
[TD]Item[/TD]
[TD][/TD]
[TD]Fruit1[/TD]
[/TR]
[TR]
[TD]I love apple[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD]watermelon[/TD]
[/TR]
[TR]
[TD]Apple is sweet fruit[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD]cherry[/TD]
[/TR]
[TR]
[TD]Banana is rich in carbohydrate[/TD]
[TD]banana[/TD]
[TD][/TD]
[TD]banana[/TD]
[/TR]
[TR]
[TD]apple / banana comes in fruit category[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD]apple[/TD]
[/TR]
[TR]
[TD]banana / watermelon is my favorite[/TD]
[TD]banana[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]watermelon / cherry / apple is nice for health[/TD]
[TD="colspan: 2"]watermelon[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]watermelon / apple[/TD]
[TD="colspan: 2"]watermelon[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Can this is possible?

 

[Jonmo1]

Which version of Excel are you using?

 

[Sid_2987]

2010

 

[Jonmo1]

Try
=MID(A2,AGGREGATE(15,6,SEARCH(fruit,A2),1),FIND(" ",A2&" ",AGGREGATE(15,6,SEARCH(fruit,A2),1))-AGGREGATE(15,6,SEARCH(fruit,A2),1))

It's ugly, but works.
I've been unable to come up with anything simpler.
Seems like it would be easier than that though.

 

[Sid_2987]

Thankyou Jonmo1. I will check it tomorrow. Thanks for your support and keeping patience for my query.

 

[Joe4]

Cross-posted: Text function

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: Excelguru Help Site - A message to forum cross posters).