Hello,

First, I apologize if this has been answered already. I did some searching and had no luck finding a solution.

Here's what I'm looking to do: I have a long list of text strings, and i'm looking to identify certain keywords, and report back those keywords.

EXAMPLE:
Column A is full of product descriptions:

Black Otterbox Case for iPhone
Blue Speck case for iphone
Pink Otterbox case for GS3
White Case Mate case for iphone 5
Blue Otterbox case for iphone 4s

Column B are the "brands" i'm searching for:
OtterBox
Case Mate
Speck

I would like to have a formula that searches for words in Column B located in Column A, then report back that word.

Example, if A1 was "Black Otterbox case for iPhone", i would want the result in column c to read "OtterBox"

Make sense? Thanks in advance for any help you can provide!!

Hi otteraaron

Try the following in C1 and copy down to C5:

=LOOKUP(20^20,SEARCH($B$1:$B$3,$A1),$B$1:$B$3)

Kevin

That works!! THANK YOU!!

hi,

Am Mon, 18 Mar 2013 15:37:58 +0000 schrieb otteraaron:

Column A is full of product descriptions:

Black Otterbox Case for iPhone
Blue Speck case for iphone
Pink Otterbox case for GS3
White Case Mate case for iphone 5
Blue Otterbox case for iphone 4s

Column B are the "brands" i'm searching for:
OtterBox
Case Mate
Speck

if you only have 3 brands you can use:
=IF(ISNUMBER(FIND("Otter",A1)),$B$1,IF(ISNUMBER(FI ND("Case",A1)),$B$2,IF(ISNUMBER(FIND("Speck",A1)), $B$3,"")))

But if you have more then do it with a UDF:

Function myString(rngC As Range) As String
Dim LRow As Long
Dim c As Range

LRow = Cells(Rows.Count, 2).End(xlUp).Row
For Each c In Range("B1:B" & LRow)
If InStr(LCase(rngC), LCase(c)) 0 Then
myString = c
Exit For
Else
myString = ""
End If
Next
End Function

Then you can call it in C1 with =myString(A1) and copy down.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Monday, March 18, 2013 8:37:58 AM UTC-7, otteraaron wrote:
Hello,



First, I apologize if this has been answered already. I did some

searching and had no luck finding a solution.



Here's what I'm looking to do: I have a long list of text strings, and

i'm looking to identify certain keywords, and report back those

keywords.



EXAMPLE:

Column A is full of product descriptions:



Black Otterbox Case for iPhone

Blue Speck case for iphone

Pink Otterbox case for GS3

White Case Mate case for iphone 5

Blue Otterbox case for iphone 4s



Column B are the "brands" i'm searching for:

OtterBox

Case Mate

Speck



I would like to have a formula that searches for words in Column B

located in Column A, then report back that word.



Example, if A1 was "Black Otterbox case for iPhone", i would want the

result in column c to read "OtterBox"



Make sense? Thanks in advance for any help you can provide!!









--

otteraaron

Give this a try where E10 holds the word OTTERBOX or SPECK etc.
And the list of stuff you are searching begins in A12.
Pull the formula down.
Change the lookup word in E10 to suit.

=IF(SUMPRODUCT(1*NOT(ISERROR(FIND($E$10,A12))))=1, $E$10,"")

Regards,
Howard

PS: This is a modified version of a formula from my archives that I use in a home built project, and if my life dependened on it, I could not tell why it or how it works.

Hi otteraaron

No problem and thanks for the feed back.

Kevin

On Mon, 18 Mar 2013 15:37:58 +0000, otteraaron wrote:


Hello,

First, I apologize if this has been answered already. I did some
searching and had no luck finding a solution.

Here's what I'm looking to do: I have a long list of text strings, and
i'm looking to identify certain keywords, and report back those
keywords.

EXAMPLE:
Column A is full of product descriptions:

Black Otterbox Case for iPhone
Blue Speck case for iphone
Pink Otterbox case for GS3
White Case Mate case for iphone 5
Blue Otterbox case for iphone 4s

Column B are the "brands" i'm searching for:
OtterBox
Case Mate
Speck

I would like to have a formula that searches for words in Column B
located in Column A, then report back that word.

Example, if A1 was "Black Otterbox case for iPhone", i would want the
result in column c to read "OtterBox"

Make sense? Thanks in advance for any help you can provide!!

It appears that you want C1 to show the Brand in the string in A1.

With your brands listed in column B1:B3

C1: =LOOKUP(2,1/ISNUMBER(SEARCH($B$1:$B$3,A1)),$B$1:$B$3)

Adjust the B1:B3 range to encompass your full list of brands.

This approach can cause problems if you have two brands that have very similar names, where one is contained within the other.
For example, consider two different brands:

Otter
Otterbox

In this case, both Otter, and Otterbox would be "found" in A1. However, the formula only returns the last valid match. One solution is, when you have such a situation, be sure to list the longer brand below the shorter brand in the list.