How can I search for words in a list with a macro?

[Ahreum]

Hi:

I need a looping macro that goes through a column to find strings that
contain certain words.

For example) Column G contains a list of person and business names,
and I'd like to identify records that contain the words,
"corporation," "technology," "furniture," "financial," "supermarket,"
etc., all at once. Since Column G has thousands and thousands of rows,
I can't run a query for each word -- so it's really important that I
can specify many different words on query run.

When a match is found (let's say cell G5), then the corresponding H
column (cell H5) will have a "1" or some other distinction for the
match.

Thanks!

 

[Don Guillett Excel MVP]

Hi:

I need a looping macro that goes through a column to find strings that
contain certain words.

For example) Column G contains a list of person and business names,
and I'd like to identify records that contain the words,
"corporation," "technology," "furniture," "financial," "supermarket,"
etc., all at once. Since Column G has thousands and thousands of rows,
I can't run a query for each word -- so it's really important that I
can specify many different words on query run.

When a match is found (let's say cell G5), then the corresponding H
column (cell H5) will have a "1" or some other distinction for the
match.

Thanks!

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Ron Rosenfeld]

Hi:

I need a looping macro that goes through a column to find strings that
contain certain words.

For example) Column G contains a list of person and business names,
and I'd like to identify records that contain the words,
"corporation," "technology," "furniture," "financial," "supermarket,"
etc., all at once. Since Column G has thousands and thousands of rows,
I can't run a query for each word -- so it's really important that I
can specify many different words on query run.

When a match is found (let's say cell G5), then the corresponding H
column (cell H5) will have a "1" or some other distinction for the
match.

Thanks!

You can do this without a macro. For example:

H1:
=OR(ISNUMBER(SEARCH({"corporation","technology","furniture","financial","supermarket"},G1)))
will return TRUE if any of the words in the list are in G1, and FALSE
if not.

You could use this in an IF statement to flag the positives however
you like. Or you could use it in a conditional formatting equation to
highlight the cells with those values.

 

[Don Guillett Excel MVP]

You can do this without a macro.  For example:

H1:
=OR(ISNUMBER(SEARCH({"corporation","technology","furniture","financial","su­permarket"},G1)))
will return TRUE if any of the words in the list are in G1, and FALSE
if not.

You could use this in an IF statement to flag the positives however
you like.  Or you could use it in a conditional formatting equation to
highlight the cells with those values.- Hide quoted text -

Option Explicit
Sub findwords()
Dim c As Range
For Each c In Selection
If InStr(c, "corporation") > 0 Or _
InStr(c, "technology") > 0 Or _
InStr(c, "furniture") > 0 Or _
InStr(c, "furniture") > 0 Or _
InStr(c, "supermarket") > 0 Then
MsgBox c.Row
End If
Next c
End Sub> - Show quoted text -

 

[Ron Rosenfeld]

You can do this without a macro. For example:

H1:
=OR(ISNUMBER(SEARCH({"corporation","technology","furniture","financial","supermarket"},G1)))
will return TRUE if any of the words in the list are in G1, and FALSE
if not.

You could use this in an IF statement to flag the positives however
you like. Or you could use it in a conditional formatting equation to
highlight the cells with those values.

I forgot to indicate that the formula above needs to be entered as an
array formula.


This formula must be **array-entered**:

=OR(ISNUMBER(SEARCH({"corporation","technology","furniture","financial","supermarket"},G1)))
----------------------------------------


To **array-enter** a formula, after entering the formula
into the cell or formula bar, hold down<ctrl><shift> while
hitting <enter>. If you did this correctly, Excel will
place braces {...} around the formula.

 

[Ahreum]

You can do this without amacro.  For example:

H1:
=OR(ISNUMBER(SEARCH({"corporation","technology","furniture","financial","supermarket"},G1)))
will return TRUE if any of thewordsin thelistare in G1, and FALSE
if not.

You could use this in an IF statement to flag the positives however
you like.  Or you could use it in a conditional formatting equation to
highlight the cells with those values.

Thanks Ron!

Made some little adjustments, and it works great!
I use the =isnumber(search()) function all the time, but I didn't
realize you can specify more than one word.
Anyway, here it is for anyone who'd like to see:

=OR(ISNUMBER(SEARCH({"*corp*","*tech*","*f
urniture*","*finan*","*market*"},A2)))

 

[Ahreum]

Option Explicit
Sub findwords()
Dim c As Range
For Each c In Selection
If InStr(c, "corporation") > 0 Or _
InStr(c, "technology") > 0 Or _
InStr(c, "furniture") > 0 Or _
InStr(c, "furniture") > 0 Or _
InStr(c, "supermarket") > 0 Then
MsgBox c.Row
End If
Next c
End Sub> - Show quoted text -

Hi Don:
This is very helpful.
Instead of MsgBox c.Row, how do I make the results write to the next
column?

Thank you!

 

[Ron Rosenfeld]

Thanks Ron!

Made some little adjustments, and it works great!
I use the =isnumber(search()) function all the time, but I didn't
realize you can specify more than one word.
Anyway, here it is for anyone who'd like to see:

=OR(ISNUMBER(SEARCH({"*corp*","*tech*","*f
urniture*","*finan*","*market*"},A2)))

Glad to help. Thanks for the feedback.