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]

Rick,
Back to the "CountExactWords" UDF again...I have done more "testing" and wonder if you could tweak your VBA code to add some more features for me?

Could you get the UDF to allow for case sensitive searching of words with hyphens and/or ellipsis attached to them?
e.g. words like "test-house" would only return a count of "1" for the explicit search "test-house", and not add "1" to the count for seaches "test" or "house" each.
e.g. text like "test...testing" would only return a count of "1" for the explicit search "test...testing", and not add "1" to the count for seaches "test" or "testing" each.

Below is a TEST TABLE that I use to experiment with.
Take a look at the "Actual Result" versus the "Expected Result" - I need both count values to be the same.

I know I didn't ask for this in the original thread post, but I've now realised that it's an oversight on my part, and I do need to allow for words with hyphens and ellipsis in them. Thanks.

TEST TABLE covers cells A1:E21 with Data in cells A2 + B2:E21.

TEXT to be SEARCHED	Expected RESULT	SEARCH TEXT	Actual RESULT	ALERT Message
Test, Test; Test? Tests, Tests; Tests? Test's, Test's; Test's? Tests', Tests'; Tests'? TEST, TEST; TEST? TESTS, TESTS; TESTS? TEST'S, TEST'S; TEST'S? TESTS', TESTS'; TESTS'? test, test; test? tests, tests; tests? test's, test's; test's? tests', tests'; tests'? Test-Test, Test-test; Test-TEST; TEST-Test, TEST-test; TEST-TEST! test...test; tests...tests! test...Test, tests...Tests.	3	Test	9	Incorrect
	3	Tests	4	Incorrect
	3	Test's	3	Correct
	3	Tests'	3	Correct
	3	TEST	8	Incorrect
Formula in Cell D2 > =CountExactWords($A$2,C2,1)	3	TESTS	3	Correct
	3	TEST'S	3	Correct
Formula in Cell E2 > =IF(D2>B2,"Incorrect","Correct")	3	TESTS'	3	Correct
	3	test	8	Incorrect
	3	tests	6	Incorrect
	3	test's	3	Correct
	3	tests'	3	Correct
	1	Test-Test	1	Correct
	1	Test-test	1	Correct
	1	Test-TEST	1	Correct
	1	TEST-Test	1	Correct
	1	TEST-test	1	Correct
	1	TEST-TEST	1	Correct
	1	test...test	1	Correct
	1	tests...tests	1	Correct

<tbody>
</tbody>

 

[István Hirsch]

If I can follow your table correctly, for the second line in col 1 „Tests? Test's, Test's; Test's?” for the search word „Tests” you expect 3 hits. Why?

 

[István Hirsch]

I think I understand your table now. The first two lines give 3 hits for "Test".

 

[DataQuestioner]

Apologies for my table quality in #41 post.

When I added it, it showed the grid in my input box...don't know why it isn't showing in the finished post.

 

[Rick Rothstein]

Rick,
Back to the "CountExactWords" UDF again...I have done more "testing" and wonder if you could tweak your VBA code to add some more features for me?

Could you get the UDF to allow for case sensitive searching of words with hyphens and/or ellipsis attached to them?
e.g. words like "test-house" would only return a count of "1" for the explicit search "test-house", and not add "1" to the count for seaches "test" or "house" each.
e.g. text like "test...testing" would only return a count of "1" for the explicit search "test...testing", and not add "1" to the count for seaches "test" or "testing" each.

See if this version of my function does what you want...

Function CountExactWords(RangeToSearch As Variant, 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 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

Hello Rick.
Yes I have re-ran the UDF many times now and discovered the following re "run time speed" >
When Excel 2010 is run for the first time (from app launch) the UDF is super fast...less than a second.
If Excel has ran multiple searches with the UDF, about 20 or more, the run time slowed to 8-10secs.

I am still confused on this problem. Are you saying you have 20 formulas in 20 cells each of which call my function, or are you saying that you have one formula that you change the lookup word 20 times?

 

[DataQuestioner]

I am still confused on this problem. Are you saying you have 20 formulas in 20 cells each of which call my function, or are you saying that you have one formula that you change the lookup word 20 times?

The latter, Rick.

 

[Rick Rothstein]

The latter, Rick.

Will you ever do the former, or is your usage always going to be the latter? If always the latter, I may have an idea to try.

Also, did you get a chance to try the revised code I posted in Message #45 yet (I think it properly handles your dash and ellipsis requirement)?

 

[DataQuestioner]

Rick,
Your amended UDF in post #45 is a triumph!
I've said it before and I'll say it again...you're a genius.

Many thanks for helping me with these challenges.

 

[DataQuestioner]

Will you ever do the former, or is your usage always going to be the latter? If always the latter, I may have an idea to try.

It's always the latter.
I'm looking forward to seeing your idea. Thanks again.

 

[DataQuestioner]

Will you ever do the former, or is your usage always going to be the latter? If always the latter, I may have an idea to try.

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