Counting occurrences of specific text within cells within a range

anglais428

Well-known Member
Joined
Nov 23, 2009
Messages
634
Office Version
  1. 2016
Platform
  1. Windows
I am looking for a formula to solve the following problem. Say I have a data range of A2:A6

Cell (A1)Name
A2Manchester, UK | Chester, UK
A3Chester
A4Manchester, UK
A5Chesterfield, UK
A6Chester, UK

<tbody>
</tbody>

I would like for the result to be '3' as Chester is in A2, A3 and A6.
The problem I am having is when 'chester' is part of another word - I want it to exclude the likes of Manchester, UK and Chesterfield, UK.
So it is almost requiring an exact match within a partial string match.
Any help would be appreciated.

Either formula or VBA to solve the problem would be fine.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi, try with the following vba code :


Code:
Sub test()
    Dim strVar, str As Variant
    Dim c, rng As Range
    Dim strToFind As String
    Dim i As Double
    i = 0
   
    Set rng = Range("A2:A6")
    strToFind = "Chester"
   
    For Each c In rng
   
        strVar = Split(c.Value, " ")
        For Each str In strVar
       
            If UCase(Replace(Replace(str, ",", ""), "|", "")) = UCase(strToFind) Then i = i + 1
           
        Next str
   
    Next c
   
    MsgBox i & " Occurences of """ & strToFind & """"
   
End Sub
 
Upvote 0
With your current dataset following formula works.

=SUMPRODUCT(ISNUMBER(SEARCH(" chester ",SUBSTITUTE(SUBSTITUTE(" "&B2:B6&" ","|",REPT(" ",99)),",",REPT(" ",99))))*1)
 
Last edited:
Upvote 0
Great, thank you both. They work well. I am using the formula approach.
 
Upvote 0
Hi,

I might be wrong, but I don't think the two REPT 99 Spaces are necessary, a single space should do the job, so taurean's formula can be simplified to this:

=SUMPRODUCT(ISNUMBER(SEARCH(" chester ",SUBSTITUTE(SUBSTITUTE(" "&B2:B6&" ","|"," "),","," ")))*1)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

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