Counting occurrences of specific text within cells within a range

[anglais428]

I am looking for a formula to solve the following problem. Say I have a data range of A2:A6

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cell (A1)[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]Manchester, UK | Chester, UK[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]Chester[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]Manchester, UK[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]Chesterfield, UK[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD]Chester, UK[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[louisH]

Hi, try with the following vba 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

 

[taurean]

With your current dataset following formula works.

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

 

[anglais428]

Great, thank you both. They work well. I am using the formula approach.

 

[jtakw]

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)

 

[Aladin Akyurek]

=SUMPRODUCT(ISNUMBER(SEARCH(" "&C1&","," "&A2:A6&","))+0)

where C1 = chester with any punctuation.

 

[anglais428]

Thanks Aladin, I actually did modify the formula to this.

 

[Aladin Akyurek]

Thanks Aladin, I actually did modify the formula to this.

Great. Thanks for the update.