How do I use countif/sumif with concatenate for a word occuring in various positions of a query?

Question

I'm using Excel and VBA. I have a list of search queries and a list of words that appear in the search queries. I'm trying to find a way to get the count of queries that a particular word appears in, as well as sum the cost and revenue of the queries that a particular word appears in. I'm using countif and sumif with a nested concatenate formula. I'm trying to figure out how to include all instances in which the word can appear in the search query--either alone, at the beginning, middle, or end--but also want to exclude instances where the word occurs as part of a longer word. Here's an example:

Let's say the word is "sand" and these are the search queries:

"sand"

"sand for sale"

"where to buy sand for sale"

"buy bulk sand"

"ham sandwiches"

Each search query has an associated cost and revenue. I can use the concatenate function to capture queries containing "sand" by including each of the following: (if the word "sand" is in cell A1)

"sand for sale" concatenate(A1," ","*")

"where to buy sand for sale" concatenate("*"," ",A1," "," *")

"buy bulk sand" concatenate("*"," ",A1)

If I do a countif or sumif pointing to A1 without a space and * before or after, it will include the values for the "ham sandwich" search query. How then do I include search queries that are comprised solely of the word, without a space before or after?

Answer 1

You can use a Regular Expression with word boundary tokens. A word boundary token detects the boundary between a word character and a non-word character (or the beginning or end of the string or line). In VBA regular expressions, a word character is in the class [A-Za-z0-9_].

Something like the following will return a Boolean indicating the presence or absence of SearchWord in SearchString:

---

Option Explicit
Function regexFind(SearchWord As String, SearchString As String) As Boolean
    Dim RE As Object

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = False
    .Pattern = "\b" & SearchWord & "\b"
    .ignorecase = True
    regexFind = .test(SearchString)
End With

End Function

---

Answer 2

As for a simple solution something like this works and won't count the ham sandwich.

=COUNTIF(A1:A5;A1)+COUNTIF(A1:A5;"* "&A1&" *")+COUNTIF(A1:A5;"* "&A1)+COUNTIF(A1:A5;A1&" *")

Where A1 is the string that is counted in this case "sand".

Answer 3

...+ COUNTIF(range,"sand")

Note that if you've tried this and it isn't working you may have "sand " where you think you have "sand"