Identifying text in a string

[otteraaron]

Hello,

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

Here's what I'm looking to do: I have a long list of text strings, an
i'm looking to identify certain keywords, and report back thos
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
located in Column A, then report back that word.

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

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

 

[Claus Busch]

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(FIND("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

 

[lhkittle]

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

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.

 

[Kevin@Radstock]

Hi otteraaron

No problem and thanks for the feed back.

Kevin

 

[Ron Rosenfeld]

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.