COUNTSUBSTRING

Returns the number of times a substring occurs in a cell.


Remarks

* For instructions on how to add a function to a workbook refer to the page under Inserting Functions
* 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
link formulas - count-substring-occurrences
link formulas - count specific words
'* '=(LEN(text)-LEN(SUBSTITUTE(text,substring,"")))/LEN(subtring)
* The equivalent JavaScript function is 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


COUNTSUBSTRING_ACROSSCELLS




© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext