CODEWITHSUNDEEP
excelvba
Ramkrishna Bhandare
Ramkrishna Bhandare

Reputation: 111

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?

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: enter image description here

Upvotes: 0

Views: 52

Answers (3)

pgSystemTester
pgSystemTester

Reputation: 9932

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 enter image description here

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

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,"))

Upvotes: 1

Dominique
Dominique

Reputation: 17565

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.

Upvotes: 0

Related Questions