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?

Report •

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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#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!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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.

http://www.mrexcel.com/forum/excel-...


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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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 solveable

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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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 •

Ask Question