Search for specific word in one cell, then paste that "key word" in an adjacent cell???

[carlp83]

I am trying to search the contents of Column B for specific words, then insert the "key word" into the adjacent cell in Column C...this will form cetegories i can group by.

For example Column B has actionable content..."Achieve Monthly Revenue Target", "Achieve <2% Overtime", "Reduce Overtime per FTE", "Reduce Supply Costs in X Department"...etc.

I have about 15 Key words...Reduce, Achieve, Implement..etc that I will be looking for in Column B. So if Cell B2 says "Reduce Overtime per FTE", I would want Reduce inserted into Cell C2...if Cell B3 said Achieve Monthly Revenue Target", I would want Achieve inserted into Cell C3.

More or less, if a cell in Column B contains x, insert x into the adjacent cell in Column C.

I'm open for any suggestions. Thanks in advance

 

[T. Valko]

I am trying to search the contents of Column B for specific words, then insert the "key word" into the adjacent cell in Column C...this will form cetegories i can group by.

For example Column B has actionable content..."Achieve Monthly Revenue Target", "Achieve <2% Overtime", "Reduce Overtime per FTE", "Reduce Supply Costs in X Department"...etc.

I have about 15 Key words...Reduce, Achieve, Implement..etc that I will be looking for in Column B. So if Cell B2 says "Reduce Overtime per FTE", I would want Reduce inserted into Cell C2...if Cell B3 said Achieve Monthly Revenue Target", I would want Achieve inserted into Cell C3.

More or less, if a cell in Column B contains x, insert x into the adjacent cell in Column C.

I'm open for any suggestions. Thanks in advance

Try this...

Keywords in F2:F3

• F2 = Reduce
• F3 = Achieve

Then, enter this formula in C2 and copy down as needed:

=LOOKUP("zzzzz",CHOOSE({1,2},"",LOOKUP(1E100,SEARCH(F$2:F$3,B2),F$2:F$3)))

If a cell contains more than one keyword the formula will return the keyword that is furthest down in the list of keywords.

 

[carlp83]

Thanks Biff, It worked like a charm. All I had to do was modify the range in the formula to include all my key words!

 

[T. Valko]

Thanks Biff, It worked like a charm. All I had to do was modify the range in the formula to include all my key words!

You're welcome. Thanks for the feedback!

 

[carlp83]

Try this...



Keywords in F2:F3

• F2 = Reduce
• F3 = Achieve

Then, enter this formula in C2 and copy down as needed:

=LOOKUP("zzzzz",CHOOSE({1,2},"",LOOKUP(1E100,SEARCH(F$2:F$3,B2),F$2:F$3)))

If a cell contains more than one keyword the formula will return the keyword that is furthest down in the list of keywords.

Biff,
Quick question. Everything seems to be working great but I stumbled across a small issue.

Some of the "Key words" I am looking for are abbreviations for Departments such as ED, OR, etc...

The column I am searching for the Key Words values like ED Satisfaction and Construction Progress of ED Department... as well as values like Completed Construction and Participated in Meeting.
When the formula looks through these values, for ED, it will pick up all of these cells because "Completed" and "Participated"

I tried putting a space at the beginning and end of the Key Word " Ed " so that it wouldn't find "ed" at the end of words, but only stand alone "ED".

Since I did the space, it prevented the items that begin with ED from being picked up since there wasn't a space...ED Satisfaction.

Any tips of how to make "ED" distinct so that only stand alone ED is picked up?