Wildcards in a pattern using a variable s in a text string to find a single value

[chazrab]

In this simple sentence which is in a userform textbox:

He he left his home and traveled eastward toward the land of the east to settle there. Although there were many beasts in that
area he still wanted to live east of where he came from, becoming the least favorite of all his brothers.

The sentence above has five occurrences containing 'east'. Two, eastward and beasts have 'east' contained within the word.
I just want to find all occurenes of the single, standalone word 'east', not contained within any other words.
This code finds every single occurrence:

"*" & s & "*"

eastward
east
beasts
east
least
The correct wildcard pattern would give only two occurrences of 'east':
east
east

Worked on this using many combinations of quotes, asterikes, etc., still not getting the correct wildcard pattern.
The variable s is the value taken from a userform textbox in the image below
Dim s As String
s = Me.TextBox1.Value
cnt = Application.CountIf(Range("E1", Range("E" & Rows.count).End(xlUp)), "*" & s & "*")

Thanks for anyone's help.
cr

 

[Peter_SSs]

I have not used a userform textbox but here are a couple of ideas to try. It is also not clear whether your count should be case-sensitive or not.

Sub Count_v1()
  Dim s As String, myword As String
  Dim cnt As Long
 
  myword = "east"
  s = "He he left his home and traveled eastward toward the land of the east to settle there. Although there were many beasts in that area he still wanted to live east of where he came from, becoming the least favorite of all his brothers."
  s = " " & Replace(s, " ", "  ") & " "
  cnt = (Len(s) - Len(Replace(s, " " & myword & " ", ""))) / (Len(myword) + 2)
End Sub

Sub Count_v2()
  Dim s As String, myword As String
  Dim cnt As Long
 
  myword = "east"
  s = "He he left his home and traveled eastward toward the land of the east to settle there. Although there were many beasts in that area he still wanted to live east of where he came from, becoming the least favorite of all his brothers."
  With CreateObject("VBScript.RegExp")
    .Global = True
    .IgnoreCase = True
    .Pattern = "\b" & myword & "\b"
    cnt = .Execute(s).Count
  End Wit

In this sort of task, punctuation can often be a problem. For example, the first code above would count zero for east if the text was "He travelled east."

 

[chazrab]

Hi Peter thx for quick response - what seems simple has been a labor of love - may I make a comment - how else would an Excel or any other
app whether in C# or anything else accept a user input value and processed as a variable in any other way than the traditional "enter any value in the
textbox and press enter or Find value"? Maybe I'm missing something. Anyway, this code line came from another experienced Excel member and there seems to be no issues with case sensitivity.
Thx very much for taking time to help with this.
cr

 

[Peter_SSs]

how else would an Excel or any other
app whether in C# or anything else accept a user input value and processed as a variable in any other way than the traditional "enter any value in the
textbox and press enter or Find value"?

I have no idea what you are getting at.

Thx very much for taking time to help with this.

You are welcome.