Search for specific word in VBA string

Question

There is a code I am using to search for specific words in a VBA string. Currently I'm using InSTR:

tag1 = InStr(1, dish, keyword, vbTextCompare)

However the challenge is that the search is comparing strings and not words. E.g. If I'm searching for "egg" as a keyword in a string - Eggplant Pizza, it's returning a true value.

Ideally I would simply like to search if the word "egg" exists in a string. Is there a better function I can use?

you know Google?!?! if you searc "excel vba find exact word in string" the 3th result it's a possible way to solve your ploblem http://stackoverflow.com/questions/28100969/excel-exact-word-matching — Fabrizio, Aug 19 '16 at 07:10

score 0 · Answer 1 · edited May 23 '17 at 11:53

0

You could also use Regular Expressions to achieve this in VBA.

Looking specifically at the ^ and $ operators to force the full word match.

So in your case something like ^Egg$ as the pattern should do what you want.

See here for some good help on this: How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

edited May 23 '17 at 11:53

Community

1
1

answered Aug 19 '16 at 09:57

Jpad Solutions

332
1
12

The answer above this one is not correct. I cannot comment under it. That will return 1, i.e. find the instance of Egg that the OP is trying to NOT find – Jpad Solutions Aug 19 '16 at 11:13

score 0 · Answer 2 · answered Aug 19 '16 at 10:20

InStr is the way to go. For finding out how many times a string is present in a text, you can use this one-line code. Here I use your example.

Debug.Print UBound(Split("Eggplant Pizza", "Egg"))

To make the code case insensitive, you can put Option Compare Text on top of your code module.

Search for specific word in VBA string

2 Answers2