Solved Formula/macro to search cell text and assign value in exce?

January 30, 2014 at 07:59:00
Specs: Windows 7
 Sorry if this question has been asked before, I couldn't find the answer after hours of googling:I am trying to get a code which searches each row for a keyword, and then assigns a value to it depending on what group of keywords it is.For example, if the Name or Comment mentions certain fruit keywords, I want it to assign the value: "Fruit", and if it mentions certain vegetable keywords, I want to assign it "Vegetables", and if it is unrelated to either, I want to keep it blank. If the name/comment mentions both fruit and vegetables then I want it to say "Error".Since the keywords I am using are mostly two letters, I want the search to only search for the two letters on its own, and not as part of a sentence. e.g. if the key word is ED, I don't want it to return a value for "I rested". Instead, I want it to stay blank.i.e. I want the code to take information from column A and B, and assign a value based on the keywords in column D and E. As an example:```A B C 2 Name Comment Value 3 V1 Eating vegetables are good for you Vegetable 4 null lock out of ep. Fruit 5 S-1 It is JUST 6 Fruit-sp Error D E 2 Fruit Vegetable 3 EP SW 4 KG SP 5 ST CM 6 BV LP```The formula I used so far is this: where C3 is the first value C3=IF(OR(ISNUMBER(LOOKUP(100,SEARCH(Sheet1!\$D\$2:\$D\$6,A3))),ISNUMBER(LOOKUP(100,SEARCH(Sheet1!\$D\$2:\$D\$6,B3)))),"Fruit","")&IF(OR(ISNUMBER(LOOKUP(100,SEARCH(Sheet1!\$E\$2:\$E\$6,A3))),ISNUMBER(LOOKUP(100,SEARCH(\$E\$2:\$E\$6,B3)))),"Vegetable","")In columns D and E I put a leading and trailing space between each word to only search for the keywords themselves, and not as part of a word. (e.g. ST is correct instead of haSTe).This formula seems to work only for 60% of the information, problems occur when the keyword is at the end of comment sentence: the value is blank. e.g. in row B4 of the table I made above my formula returns a blank.I can't figure out how to fix this. Maybe a macro would be more suited to solve this problem? However I am not too savvy in VBA.Thanks message edited by shibbyshibby

See More: Formula/macro to search cell text and assign value in exce?

✔ Best Answer
February 3, 2014 at 07:46:33
 I thought I posted a response but I don't see it, so I'll try again.Either I'm confused or there are more issues with your formula that you have mentioned.In all cases below, the underscore represents a space.1 - Let's deal with C4 first:If you expand your Keyword table as follows and update your formula, you will get Fruit in C4:```Fruit Vegetable _EP_ _SW_ _KG_ _SP_ _ST_ _CM_ _BV_ _LP_ _EP. _SW. _KG. _SP. _ST. _CM. _BV. _LP. ```2 - As far as C6, I'm not sure how you expect to return the word Error, since it it not included in your formula as a possible return value.Please explain.3 - You said you are looking for standalone keywords only, yet you want "Vegetable" returned in C3. I don't see a standalone "_vegetable_" in A3:B3. I see "_vegetables_", with an "s", but not a standalone "_vegetable_".Please explain.

#1
January 30, 2014 at 08:34:23
 First, a posting tip: Before posting example data in this forum, please click on the blue line at the end of this post and read the instructions on how to format example data so that it is easier for us to read. As for your question, since it's hard for us to tell which column contains what data/sentence, it's hard for us to set up a test sheet and play around.Please repost your example data after reading the instructions found via the following link. Thanks!

Report •

#2
January 30, 2014 at 09:04:12
 Sorry, the table formatting is sort of weird, I posted the same question in a different forum here, where the table formatting is more clear.

Report •

#3
January 30, 2014 at 12:37:26
 I'm still a tad confused. Are the values you show in Column C the values that your formula returned or the values that you want the formula to return?I started with this:``` A B C 1 2 Name Comment Value 3 V1 Eating vegetables are good for you 4 null lock out of ep. 5 S-1 It is JUST 6 Fruit-sp D E 1 (The underscores represent spaces) <------- 2 Fruit Vegetable 3 _EP_ _SW_ 4 _KG_ _SP_ 5 _ST_ _CM_ 6 _BV_ _LP_```Using your formula in C3:C6 I got this:``` C 1 2 Value 3 Vegetable 4 5 6 Fruit```

Report •

Related Solutions

#4
January 30, 2014 at 14:50:26
 The values I showed in column C are the ones I want returned, however, as you can see (and tried), when using my formula C4 returns a blank, instead of the intended answer: "Fruit"message edited by shibbyshibby

Report •

#5
February 3, 2014 at 06:54:12
 bump, hopefully this problem is solveablemessage edited by shibbyshibby

Report •

#6
February 3, 2014 at 07:46:33
✔ Best Answer
 I thought I posted a response but I don't see it, so I'll try again.Either I'm confused or there are more issues with your formula that you have mentioned.In all cases below, the underscore represents a space.1 - Let's deal with C4 first:If you expand your Keyword table as follows and update your formula, you will get Fruit in C4:```Fruit Vegetable _EP_ _SW_ _KG_ _SP_ _ST_ _CM_ _BV_ _LP_ _EP. _SW. _KG. _SP. _ST. _CM. _BV. _LP. ```2 - As far as C6, I'm not sure how you expect to return the word Error, since it it not included in your formula as a possible return value.Please explain.3 - You said you are looking for standalone keywords only, yet you want "Vegetable" returned in C3. I don't see a standalone "_vegetable_" in A3:B3. I see "_vegetables_", with an "s", but not a standalone "_vegetable_".Please explain.

Report •

#7
February 3, 2014 at 10:03:30
 1. That is a good solution, I will add keywords to my table.2. You are right, my formula was incomplete in that regard, however I think I will ignore trying to return the value error and leave those values blank.3. You are correct, I forgot to add that to my table.I will try this out once more and hopefully this will solve my problem.

Report •