Text function

[Sid_2987]

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

Below is the example.

List Item I love apple apple apple Apple is sweet fruit apple banana Banana is rich in carbohydrate banana watermelon apple / banana comes in fruit category banana Cherry banana / watermelon is my favorite watermelon watermelon / cherry / apple is nice for health Cherry <colgroup><col><col span="3"></colgroup><tbody> </tbody>

<colgroup><col><col></colgroup><tbody>
</tbody>

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.

List	Item		Fruit1
I love apple	apple		watermelon
Apple is sweet fruit	apple		cherry
Banana is rich in carbohydrate	banana		banana
apple / banana comes in fruit category	banana		apple
banana / watermelon is my favorite	watermelon
watermelon / cherry / apple is nice for health	watermelon
watermelon	watermelon
watermelon / apple	watermelon

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

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.

List	Item		Fruit1
I love apple	apple		watermelon
Apple is sweet fruit	apple		cherry
Banana is rich in carbohydrate	banana		banana
apple / banana comes in fruit category	apple		apple
banana / watermelon is my favorite	banana
watermelon / cherry / apple is nice for health	watermelon
watermelon / apple	watermelon

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

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