|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
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
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.
message edited by shibbyshibby