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

adam akhirat

New Member
Joined
Aug 27, 2013
Messages
5
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

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

<tbody>
</tbody>
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Aug 27, 2013
Messages
5
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Aug 27, 2013
Messages
5
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
That's a whole other story, and definately not easy.
Would most likely require VBA Code.
 

adam akhirat

New Member
Joined
Aug 27, 2013
Messages
5
may i know what is the meaning of this "2^15" ?
thank you
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

Forum statistics

Threads
1,081,973
Messages
5,362,505
Members
400,677
Latest member
champchamp

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top