COUNTSUBSTRING

COUNTSUBSTRING()
Returns the number of times a substring occurs in a cell.

REMARKS
link formulas - count-substring-occurrences
link formulas - count specific words
* '=(LEN(text)-LEN(SUBSTITUTE(text,substring,"")))/LEN(substring)
Public Function COUNTSUBSTRING( _

Public Function COUNTSUBSTRING( _ 
         ByVal sText As String, _ 
         ByVal sSubString As String, _ 
Optional ByVal bCaseSensitive As Boolean = True) As Integer 

Dim itotalcells As Integer 
   
   If (bCaseSensitive = True) Then 
      itotalcells = UBound(VBA.Split(UCase(sText), UCase(sSubString))) 
   End If 
   
   If (bCaseSensitive = False) Then 
      itotalcells = UBound(VBA.Split(sText, sSubString)) 
   End If 
   
   COUNTSUBSTRING = itotalcells 
End Function 

For instructions on how to add this function to a workbook refer to the page under Inserting Functions

COUNTSUBSTRING_ACROSSCELLS

Related Formulas

* Formula Count cells that are text and begin with
* Formula Count cells that are text and end with
* Formula Count cells that are text and contain a value
* Formula Count cells that are text and contain either x or y
* Formula Count cells that are text and do not contain a value