Using VBA to find if any keyword exists in string

frolicols

New Member
Joined
May 11, 2011
Messages
15
In Excel I'm trying to find if any keyword from a list of keywords exists in a string from within an array.

I'm aware of the instr function, but does this work for searching a string from an array of keywords?

I'm not sure how many keywords I am likely to require, so ideally wanted a solution that didn't involve a loop.

Hope you can help!
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,197
You want to verify if a certain string element, that is, a keyword as you are calling it, is part of an array. OK, let's say you have an array of names like this:

Bill
Bob
Tom
Mike
Jim

That means, you can express it in VBA like this:
Code:
Dim myArray As Variant
myArray = Array("Bill", "Bob", "Tom", "Mike", "Jim")
Now let's further say you want to test if a certain string element is in that array. "Tom" is a rather regal-sounding name, so we'll use that. You can assign a string variable to it:
Code:
Dim strBelong as String
strBelong = "Tom"
Now to see if Tom exists in the array without looping as you requested, you can use Match, for example:

Code:
Sub Test1()
Dim myArray As Variant, strBelong as String
myArray = Array("Bill", "Bob", "Tom", "Mike", "Jim")
strBelong = "Tom"
On Error Resume Next
MsgBox "Yes! " & myArray(WorksheetFunction.Match(strBelong, myArray, 0) - 1) & " belongs!"
If Err = 0 Then
Exit Sub
Else
Err.Clear
MsgBox strBelong & " does not belong.", , "No such animal"
End If
End Sub
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
This can be done fairly easily without VBA

Try
=LOOKUP(2^15,SEARCH($D$2:$D$6,A2),$D$2:$D$6)

A2 is your string
D2:D6 is yourlist of keywords

Hope this helps.


Excel Workbook
ABCD
1StringFormulaKeywords
2My Name is Not Tom DudeTomBill
3Bob is 40 years oldBobBob
4Call Jim laterJimTom
5Mike
6Jim
Sheet1
 

frolicols

New Member
Joined
May 11, 2011
Messages
15
Thank you for both replies. I am not looking to use any Excel on this one as I want to complete the lookup prior to printing the information on a worksheet.

To give further information, I am downloading text messages from a server (these arrive in XML format) and I am then extracting the required information to populate into a 2D array (where one of the columns is the message itself).

I want to search the message for a number of keywords. Using Tom's example, let's say there were some messages from the server:

"Hi Tom it's Frol here! Thanks for your reply!"
"Bill, the answer is 42"
"I think you sent the message to the wrong person"
"Always to the left, especially on Tuesdays"

I'd want to search each of the messages to see if they contain "Tom" or "Bill" and return true or false, therefore returning true for my first two examples, and false for the latter two.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,197
You have a list of downloaded xml statements, each might contain one, several, or none of the keywords. The array of keywords might be that of 3 or 10 0r 100 elements. You want VBA but don't want any looping, so I could not recommend a reasonably efficient non-looping solution that would be better than a solution with loops. I suppose you could programmatically list a series of formulas with SEARCH criteria but if it is a long list of imported statements and a lot of keywords, as I say, it becomes unreasonable. Maybe someone else reading this can offer a good VBA solution that does not involve loops.
 

frolicols

New Member
Joined
May 11, 2011
Messages
15
If there is not a suitable non-looping alternative I would still love a looping answer. I can always test it and if it runs quickly, job done!

Thanks for your time Tom!
 

Forum statistics

Threads
1,085,304
Messages
5,382,831
Members
401,807
Latest member
xlWatcher

Some videos you may like

This Week's Hot Topics

Top