Using VBA to find if any keyword exists in string

[frolicols]

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]

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:

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:

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:

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]

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
	A	B	C	D
1	String	Formula		Keywords
2	My Name is Not Tom Dude	Tom		Bill
3	Bob is 40 years old	Bob		Bob
4	Call Jim later	Jim		Tom
5				Mike
6				Jim
Sheet1

 

[frolicols]

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]

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]

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!

 

[PrashanthKumar123]

hi - bumping this up in 2021 to check if there is a solution. I am facing a similar requirement!

Looping and non-looping alternatives welcome!

thanks in advance!