Find if have "gift set" in description, then put in the category "gift sets"

[adam akhirat]

Hye...i newbie here and really need helps from all the Guru here...
i want to FIND in the description if have words GIFT SET in any place, then if have, then there go to category GIFT SETS..if no words gift set, then let it empty.
i do this manually but i have 6000++ data and please,help me =D
thank you

ITEM	BRAND TITLE	Category
1	Marina De Bourbon Eau De Parfum Spray 3.4 oz
2	Taylor Swift Gift Set -- 3.4 oz Eau De Parfum Spray + 3.4 oz Body Lotion + 3.4 oz Shower Gel	Gift Sets
3	Lancome Eau De Toilette Spray 2.5 oz
4	Justin Beiber Gift Set -- 3.4 oz Eau De Parfum Spray + 3.4 oz Body Lotion + 3.4 oz Shower Gel	Gift Sets
5	Justin Beiber Eau De Parfum Spray 3.4 oz
6	Narciso Rodriguez Eau De Toilette Spray 4.2 oz

<tbody>
</tbody>

 

[Jonmo1]

Welcome to the board..

Try
=IF(ISNUMBER(SEARCH("Gift Set",A1)),"Gift Sets","")


However, if you are going to expand this to many different categories, I would look for a alternate approach like a lookup.

 

[adam akhirat]

yes it working man i love you man ! hahha
oh yeah i want to do it in many categories, like skincare, makeup, tester, samples, body lotions, after shave, after shave balm, body cream, body powder, deodorant, shower gel, soap,...

help me man thanks a lot and really appreciates it =D

Welcome to the board..

Try
=IF(ISNUMBER(SEARCH("Gift Set",A1)),"Gift Sets","")


However, if you are going to expand this to many different categories, I would look for a alternate approach like a lookup.

 

[Jonmo1]

Create a lookup Table in say D2:E10
Column D = the key words skincare makeup tester etc...
Column E = the corresponding values you want returned based on the keywords.

Then use

=LOOKUP(2^15,SEARCH($D$2:$D$10,A1),$E$2:E$E10)
A1 is the cell with the longer string.

There cannot be any blanks in D2:D10

 

[adam akhirat]

ok i will try again but if 1 products belong to more than 1 category,
can i combine it and let it like this example..
Gift Sets|Deodorant|Soap

thankz man

 

[Jonmo1]

That's a whole other story, and definately not easy.
Would most likely require VBA Code.

 

[adam akhirat]

may i know what is the meaning of this "2^15" ?
thank you

 

[Jonmo1]

2^15 = 2 to the power of 15 = 32768
32768 just happens to be 1 larger then the maximum number of characters allowed in a cell (32767)
Thus ensuring the lookup value is larger than any possible numeric result of the Search function

 

[adam akhirat]

thank man..really appreciates it =D

 

[Jonmo1]

Glad to help, thanks for the feedback.