How to extract multiple copies of the same word from a single cell?

[DataQuestioner]

This request is pretty simple >
I want to extract every occurrence of a word, via a search string, from a single cell with text.
The word(s) could occur anywhere in the cell text.
The word could occur many times, or even not at all!
The returned result should display the total count occurrence.
The returned result must be case sensitive.

Using FIND & REPLACE will not solve this - it only returns the cell count, not the word count.
Using SORT & FILTER will not solve this - it only returns the cell count, not the word count.
Using the FIND or SEARCH Functions (on their own) doesn't solve this - they only find the first occurrence within a cell.

I'm guessing that either a stacked user defined function or an array formula will provide the answer.

e.g. Cell A1 contains "This is a test which will test how to test this request."
a) If the search string was "test" then I would be expecting a returned result of "3".
b) If the search string was "this" then I would be expecting a returned result of "1" (the initial "This" would be ignored because of the case sensitive search).
c) If the search string was "testing" then I would be expecting a "word not found" response. I presume this can be done by using the IFERROR function.

Thanks.

 

[Rick Rothstein]

How's it going Rick?
Any progress on your idea to possibly make the UDF faster?

Since I cannot repeat your problem on my computer, all I can do is guess at a solution. Give this revised macro a try and let me know if it worked better for you or not....

Function CountExactWords(RangeToSearch As Range, TextToFind As String, _
                         Optional CaseSensitive As Boolean) As Long
  Dim X As Long, Words() As String
  Static SearchRangeAddress As String, Combined As String
  If SearchRangeAddress <> RangeToSearch.Address Then
    SearchRangeAddress = RangeToSearch.Address
    If RangeToSearch.Columns.Count > 1 Then
      MsgBox "Single column data only!", vbCritical
    ElseIf RangeToSearch.Rows.Count = 1 Then
      Combined = " " & RangeToSearch.Value & " "
    Else
      Combined = " " & Join(Application.Transpose(RangeToSearch), " ") & " "
    End If
  End If
  Words = Split(Combined, TextToFind, , 1 + CaseSensitive)
  For X = 0 To UBound(Words) - 1
    If Right(Words(X), 1) Like "[!A-Za-z0-9…-]" And Right(Words(X), 3) <> "..." And _
       Left(Words(X + 1), 1) Like "[!A-Za-z0-9'…-]" And Left(Words(X + 1), 3) <> "..." Then
      CountExactWords = CountExactWords + 1
    End If
  Next
End Function

 

[DataQuestioner]

Rick,
Thanks for making the amendment.
The process speed is similar to the previous...I think we should close this thread now.

The UDF is still terrific...thanks for everything.


PS Could you possibly help me over on the following thread?
http://www.mrexcel.com/forum/excel-questions/840201-highlighting-different-multiple-words-cell-range-making-them-bold-coloured.html