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.

 

[XOR LX]

Hi.

I presume you are wanting to match whole words only?

So that e.g. searching for the string "test" in:

"This is some testing which will attest to how good is testing this request."

would return 0, and not 3.

If this is the case, any feasible solution would first need to take into account all possible delimiters, i.e. punctuation, within the string. As well as spaces, then, can there be commas, colons, semi-colons, etc. in the string?

Of course, you would need to provide a definitive list of all such punctuation in order to be sure of receiving a correct solution.

Regards

 

[DataQuestioner]

XOR LX,

Yes, whole words.
Yes, there will be all of the following possible punctuation in the cell text >
"!""#$%&'()*+,¶./:;< =>?@[\]^_`{|}~"

One last thing, there could also be all of the numerals 0 to 9.

So to clarify, if the search string was "this" and the word in the cell had "this," or "this;" or "this." then those occurrences would be part of the count.
i.e. somehow remove the punctuation from the search before looking for the search string, case sensitive, whole word.

Thanks.

 

[WarPigl3t]

Are you familiar with VBA? You could create your own function.

Function myFunction(mySearch, myString)
    'After you have created this function in VBA, you can input the formula into the cell you want the results to go in.
    'eg. Say you have the text "Test test test" in cell A1.
    'In B1, you have the word or words you want to search for so you may write "test" in cell B1.
    'Cell C1 is a good place to put the formula =myFunction(A1, B1)

    'Application.Volatile allows the function to update each time a change is made to the worksheet.
    Application.Volatile
    
    'Start by searching to see if there is even one occurance of the search "test" in the string "Test test test".
    'If there is not even one occurance, return 0 as the result.
    If InStr(" " & myString & " ",  mySearch) <= 0 OR IsError(InStr(" " & myString & " ", mySearch))Then
        'If the search "test" exists in the string "Test test test", then it will return a number.  Otherwise it will either return 0, -1, or an error.  I don't know which one.  So I created an AND statement that says if there is an error, then it won't enter this if statement.  
        myFunction = 0
        Exit Function
    End If
    
    'If there is at least one occurance of the search "test" in the string "Test test test", then lets get the count.
    'UBOUND gives you the number of times it was split or in other words, it will tell you how many occurances of "test" there are.
    myFunction = UBOUND(Split(" " & myString & " ", mySearch))
End Function

No promises that this code will work.

 

[DataQuestioner]

WarPig,

I'm slowly learning VBA, but I'm nowhere near the level of detail that you've listed in your suggestion.

I'm familiar with Alt+F11 and Module setup.
I'm familiar with Alt+F8 for launching Macros.
I'm familiar with Ctrl+Shift+Enter for Array Formulas.

I'm still playing around with UDFs and trying to create stacked UDFs with multiple functions wrapped around each other.

Thanks.

 

[XOR LX]

Actually what I said isn't strictly true; there are alternative solutions which do not require that that list be defined.

For example, assuming that the first string on which you wish to perform the count is in A1, and that your search string (e.g. "test") is in B1, then if, whilst the active cell in the worksheet is somewhere in row 1, you first go to Name Manager and define:

Name: Arry1
Refers to: =ROW(INDEX($A:$A,1):INDEX($A:$A,LEN($A1)-LEN($B$1)+1))

Name: Arry2
Refers to: =COLUMN(INDEX($1:$1,2):INDEX($1:$1,LEN($B$1)+1))

Name: Arry3
Refers to: =ROW(INDEX($A:$A,1):INDEX($A:$A,LEN($B$1)))

then the required formula (e.g. in C1) is:

=SUMPRODUCT(N(MMULT(N(MID(MID(" "&$A1&" ",Arry1,LEN($B$1)+2),Arry2,1)=MID(" "&$B$1&" ",Arry2,1)),Arry3^0)=LEN($B$1)),N(MMULT(N(ABS(77.5-CODE(MID(MID(" "&UPPER($A1)&" ",Arry1,LEN($B$1)+2),(LEN($B$1)+2)^{0,1},1)))>13),{1;1})=2))

Copy down to give similar counts for strings in A2, A3, etc.

Regards

 

[DataQuestioner]

XOR LX,

That's a sporty looking nested function you've created, with multiple arrays.

I will give it a go and let you know my findings.

Thanks for taking the time to create this.

 

[DataQuestioner]

OK, XOR LX, I've played around with your three arrays and the nested function.

there is a small flaw in the result, i.e. it is not observing the "case sensitive" text string in the B1 cell.

Try doing a test on the following cell A1 text >

"Test this test by testing this testingly detested TEST with testimony!" If your B1 text string is "test" or "Test" or "TEST", then each returned answer should be "1". Your function is returning "3" for each string because it is not distinguishing the case sensitive condition. Let me know if you can make an adjustment. Thanks.

<tbody>
</tbody>

 

[XOR LX]

Ah! Completely forgot about the case-sensitive part.

Sincere apologies. Will try to rectify the solution.

Regards

 

[István Hirsch]

Try this UDF:

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