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

D

DataQuestioner

Board Regular
Joined
Sep 12, 2013
Messages
115
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.
 
Sort by date Sort by votes
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.

[TABLE="width: 575"]
<tbody>[TR]
[TD]TEXT to be SEARCHED
[/TD]
[TD]Expected
RESULT
[/TD]
[TD]SEARCH
TEXT
[/TD]
[TD]Actual
RESULT
[/TD]
[TD]ALERT
Message
[/TD]
[/TR]
[TR]
[TD]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.
[/TD]
[TD]3
[/TD]
[TD]Test
[/TD]
[TD]9
[/TD]
[TD]Incorrect
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD]Tests
[/TD]
[TD]4
[/TD]
[TD]Incorrect
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD]Test's
[/TD]
[TD]3
[/TD]
[TD]Correct
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD]Tests'
[/TD]
[TD]3
[/TD]
[TD]Correct
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD]TEST
[/TD]
[TD]8
[/TD]
[TD]Incorrect
[/TD]
[/TR]
[TR]
[TD]Formula in Cell D2 > =CountExactWords($A$2,C2,1)
[/TD]
[TD]3
[/TD]
[TD]TESTS
[/TD]
[TD]3
[/TD]
[TD]Correct
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD]TEST'S
[/TD]
[TD]3
[/TD]
[TD]Correct
[/TD]
[/TR]
[TR]
[TD]Formula in Cell E2 > =IF(D2>B2,"Incorrect","Correct")
[/TD]
[TD]3
[/TD]
[TD]TESTS'
[/TD]
[TD]3
[/TD]
[TD]Correct
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD]test
[/TD]
[TD]8
[/TD]
[TD]Incorrect
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD]tests
[/TD]
[TD]6
[/TD]
[TD]Incorrect
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD]test's
[/TD]
[TD]3
[/TD]
[TD]Correct
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD]tests'
[/TD]
[TD]3
[/TD]
[TD]Correct
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1
[/TD]
[TD]Test-Test
[/TD]
[TD]1
[/TD]
[TD]Correct
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1
[/TD]
[TD]Test-test
[/TD]
[TD]1
[/TD]
[TD]Correct
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1
[/TD]
[TD]Test-TEST
[/TD]
[TD]1
[/TD]
[TD]Correct
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1
[/TD]
[TD]TEST-Test
[/TD]
[TD]1
[/TD]
[TD]Correct
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1
[/TD]
[TD]TEST-test
[/TD]
[TD]1
[/TD]
[TD]Correct
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1
[/TD]
[TD]TEST-TEST
[/TD]
[TD]1
[/TD]
[TD]Correct
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1
[/TD]
[TD]test...test
[/TD]
[TD]1
[/TD]
[TD]Correct
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1
[/TD]
[TD]tests...tests
[/TD]
[TD]1
[/TD]
[TD]Correct
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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?
 
Upvote 0
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.
 
Upvote 0
DataQuestioner said:
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.
Click to expand...
See if this version of my function does what you want...
Code: 
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




DataQuestioner said:
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.
Click to expand...
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?
 
Upvote 0
DataQuestioner said:
The latter, Rick.
Click to expand...
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)?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Extract the word which carries the specific text
Replies
5
Views
229
Cubist
Cubist
B
  • Solved
VB for Searching for ~ 20 words in a large table of text
Replies
3
Views
121
BobTheSpreadsheetBuilder
B
B
  • Solved
Copy a single selected word in a cell into memory using vba /or sendkeys
Replies
3
Views
174
jolivanes
jolivanes
A
  • Solved
Searching for 2 different values in the same cell in a different tab
2
Replies
14
Views
605
Peter_SSs
Peter_SSs
H
  • Question Question
Formula to extract a word from a list of words
Replies
4
Views
335
shift-del
shift-del

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top