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

A

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

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."
 
D

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

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)))
 
A

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top