Using Microsoft Excel, How to check the total number of occurrences of a string in an excel column with many cells having comma-separated strings?

Data as below:

CodePudding user response：

This seems to work (assuming no spaces between comma and number):

=SUM((LEN("," & DataColumn & ",")-LEN(SUBSTITUTE("," & DataColumn & ",","," & ValueToLookFor & ",","")))/LEN("," & ValueToLookFor & ","))  

Or if your data is in a table and the value to look for is hard-coded:

=SUM((LEN("," & Table1[Column1] & ",")-LEN(SUBSTITUTE("," & Table1[Column1] & ",",",1300.01,","")))/LEN(",1300.01,"))

CodePudding user response：

I would advise an UDF (User-Defined Function), counting the amount of occurences inside a string. This can be based on the Split() VBA function: you split a string into separate parts and count the ones you're looking for.

Then, you write a VBA macro, based on that UDF for all selected cells.

CodePudding user response：

This custom function will display all the occurrences of a specified string.

Function countStringRange(theRange As Range, theString As String) As Long
Dim ws As Worksheet, cleanRange As Range, aCell As Range
Dim i As Long, theLength As Long
    Set ws = theRange.Worksheet
    theLength = Len(theString)

    Set cleanRange = Intersect(ws.UsedRange, theRange)
    
    If Not cleanRange Is Nothing Then
        For Each aCell In cleanRange.Cells
            If Not IsEmpty(aCell) Then
                For i = 1 To Len(aCell.Value) - theLength   1
                    If Mid(aCell.Value2, i, theLength) = theString Then
                        countStringRange = countStringRange   1
                    End If
                Next i
            End If
        Next aCell
    End If

End Function

Example