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,
This looks interesting.

How or where is the search string typed in?

 

[DataQuestioner]

Istvan,

I'm guessing that the syntax for your WCOUNT function is > =WCOUNT(cell,"text") ?

 

[István Hirsch]

Istvan,

I'm guessing that the syntax for your WCOUNT function is > =WCOUNT(cell,"text") ?

Yes, that’s right. After copying the code, go back the the spreadsheet, click on, say B1, then a window appears with S (now A1) and wd (test) to be entered. Hit Enter then copy down the formula as long as you have data in column A. Let me know if it does not work as required.

 

[Rick Rothstein]

Here is another UDF you can try which uses straight VBA code (no call-outs to a Regular Expression engine)...

Function CountExactWords(TextToSearch As String, TextToFind As String, _
                         Optional CaseSensitive As Boolean) As Long
  Dim X As Long, Str1 As String, Str2 As String, Pattern As String
  If CaseSensitive Then
    Str1 = TextToSearch
    Str2 = TextToFind
    Pattern = "[!0-9A-Za-z]"
  Else
    Str1 = UCase(TextToSearch)
    Str2 = UCase(TextToFind)
    Pattern = "[!0-9A-Z]"
  End If
  For X = 1 To Len(Str1) - Len(Str2) + 1
    If Mid(" " & Str1 & " ", X, Len(Str2) + 2) Like _
                      Pattern & Str2 & Pattern Then
      CountExactWords = CountExactWords + 1
    End If
  Next
End Function

There are two required are arguments... the source text (you can use a quoted text string or a cell reference containing the text), the text to find (which can also be a quoted text string or cell reference)... and one optional argument specifying whether you want the search to be case sensitive (TRUE) or not (FALSE).

 

[DataQuestioner]

Yes, that’s right. After copying the code, go back the the spreadsheet, click on, say B1, then a window appears with S (now A1) and wd (test) to be entered. Hit Enter then copy down the formula as long as you have data in column A. Let me know if it does not work as required.

I'm not getting this part!
However, if I just enter "=WCOUNT(A1,"test") into B1 then it will return a word count.

Thanks for your suggestion.

 

[István Hirsch]

Ok, if you did this way (I had wanted to describe finding the UDF function but finally had left out the matter!). Does it work as required?

 

[DataQuestioner]

Hello Rick - good to hear from you again.

I've taken your CountExactWords Function and used the following syntax > =CountExactWords(cell,"text",0 [or 1]).

It returns the word count, and as you say, choosing the true or false option on the end of the function also delivers a case sensitive result. Thanks for this.

But guess what I'm gonna say next?
Remember our previous thread about word counts where you produced a brilliant Array Formula "CharacterCount".
One of the teething problems with that formula, and now with your new function, is Punctuation - pesky little characters!

I need to search for words with apostrophes, e.g. son's or sons' (singular + plural possessives), and return an exact match for these.
Can you adjust your function to allow for this?

 

[DataQuestioner]

Hello Istvan.

The only problem I'm having with your UDF is identical to what I've just described to Rick in my previous post, i.e. words with apostrophes.

Your UDF works great with case sensitive strings, and also finds singular possessive words, e.g. son's.
However it is not coping well with a plural possessive word, e.g. sons'.

Can you adjust for this?

Apologies if I didn't explain this requirement in my opening post.

 

[István Hirsch]

Check this version to count son’s or sons’:

Function Wcount(s As String, wd As Variant) As String
With CreateObject("VBScript.Regexp")
    s = Replace(s, "'", "zyx")
    wd = Replace(wd, "'", "zyx")
    .Global = True
    .Pattern = "\b" & wd & "\b"
    Set mymatches = .Execute(s)
    Wcount = mymatches.Count
  End With
End Function

 

[Rick Rothstein]

Hello Rick - good to hear from you again.

I need to search for words with apostrophes, e.g. son's or sons' (singular + plural possessives), and return an exact match for these.
Can you adjust your function to allow for this?

So to be clear, if you are trying to count the word "son", you want these all to be counted...

son
son's
sons'

Is that correct?