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.

 

[DataQuestioner]

Istvan,
Your UDF works perfectly.
Thanks for making the apostrophe amendment.

This is a really neat UDF...so compact and accurate.

 

[DataQuestioner]

Rick,
No!

If I search for "son" I only want to return the total cell count for "son" i.e. whatever is inside the quotation marks - exclusively, nothing else.

If I search for "son's" I only want "son's"; not "sons", or "sons'" or "Sons" or "SONS" etc, etc. - explicitly what's inside the quotation marks.

 

[István Hirsch]

You are welcome, thanks for the feedback.

 

[DataQuestioner]

Istvan,
I have some more feedback for you.

I've just run the UDF on a very extensive cell search, and it has returned some concerns!
The original tests that I did were based only on a cell search of A1:A10 - pretty basic.
My current cell search involves 31,000 cells.

The UDF takes a long time to process the results - I've never seen an array, macro, or UDF take this long before!
Is there a way that you can speed up the count within the UDF?

Also, when the result is returned, and the "value" is placed in the "=WCount" cell, what format is the cell value?
I find that if I try to add up the values in the result cells that a) it takes ages b) it doesn't count the values, it only counts the number of cells!

Please advise. Thanks.

 

[DataQuestioner]

XOR LX,

Were you able to amend your nested UDF to allow for the case sensitive scenario?
I'm still interested in your solution.

 

[Rick Rothstein]

Rick,
No!

If I search for "son" I only want to return the total cell count for "son" i.e. whatever is inside the quotation marks - exclusively, nothing else.

So, if you search for son, should son's (the possessive of what you are searching for) be counted?

 

[DataQuestioner]

Rick,
No. Only "son", because that is the explicit text inside the quotation marks.

Thanks for your persistence.

 

[István Hirsch]

My approach this time was based on using a regular expression, which is relatively easy to create but slow to run. I do not think that its speed could be significantly increased with any modification.

If running time is a bottleneck, you should apply a formula or Rick’s VBA (no regular expression) in post #14 – those will be much faster.

 

[Rick Rothstein]

Here is my revised code... I changed the first argument to take the full contiguous range (vertical only), so for 31,000 cells in Column A, you would call it with A1:A31000 as the first argument when used in a formula and with Range("A1:A31000") as the first argument when called from other VB code. Note, as structured, this code will be quite fast, so the only question is whether is handle the possessive the way you want... please test that and let us know.

Function CountExactWords(RangeToSearch As Range, TextToFind As String, _
                         Optional CaseSensitive As Boolean) As Long
  Dim X As Long, Combined As String, Words() As String
  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.Value), " ") & " "
  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 _
       Left(Words(X + 1), 1) Like "[!A-Za-z0-9']" Then
      CountExactWords = CountExactWords + 1
    End If
  Next
End Function

 

[DataQuestioner]

Rick,
I've ran a test on just over 31,000 cells of text and your UDF works really well.
It takes between 8-10 secs to complete the search.

The apostrophe options are also working well.

Once again you've created a great VBA script...Thanks very much.

Another question for you >
Is it possible to replace the "search text" in quotes, as part of the UDF syntax, by pointing it to another cell where the actual search text is typed in?