Count the number of times a text is in a column

[easybpw]

Hello and thanks in advance for the help.

I have a sheet that has strings of text in column C. I need to count the number of times a specific word is in those strings. For example I could be looking for ALIBEN. That text will only be in one time in the each cell in column C but may only be in 25 of the 5000 cells I am looking at. This seems simple but I've been unable to come up with something that works.

Thanks!

Bill

 

[Ron Coderre]

As long as the word is not embedded as part of another word (CALIBEN), then something like this should work
=COUNTIF(C:C,"*ALIBEN*")

Is that something you can work with?

 

[BengalMagic]

Hello and thanks in advance for the help.

I have a sheet that has strings of text in column C. I need to count the number of times a specific word is in those strings. For example I could be looking for ALIBEN. That text will only be in one time in the each cell in column C but may only be in 25 of the 5000 cells I am looking at. This seems simple but I've been unable to come up with something that works.

Thanks!

Bill

Sub 25032013()

'globally declare variables
'
Dim myRange As Range
Dim myPattern As String
Dim myCell As Range
Dim a As Long
a = 0
'
'choose range of data to test
'
Set myRange = Range(Range("C1"), Range("C" & Rows.Count).End(xlUp))
'
'what to look for in each cell in the range (in this example, "ALIBEN" anywhere)
'
myPattern = "*ALIBEN*"
'
'turn off screen updating
'
Application.ScreenUpdating = False
'
'update status bar
'
Application.StatusBar = "Macro running, please wait...."
'
'begin routine
'
For Each myCell In myRange
    With myCell
      If (.Value Like myPattern) Then
         a = a + 1
      End If
    End With
Next
'
'turn on screen updating
'
Application.ScreenUpdating = True
'
'reset status bar
'
Application.StatusBar = False
MsgBox a
End Sub

 

[easybpw]

Yes. I tried this earlier but had an issue. ALIBEN is actually in another cell so I tried to do this: =COUNTIF(C:C,F5) but it didn't work. The way you put it works if I type what I'm looking for in the formula.

 

[vds1]

Something like this ?

=COUNTIF(C:C,"*" & F5 & "*")