CODEWITHSUNDEEP
excelvba
DBWeinstein
DBWeinstein

Reputation: 9549

How to search for specific string within a cell in excel?

I have a list of Id's in one column. sometimes the cell has a single id, sometimes a cell has multiple id's:

[ "52b46bfc763f4ad9198b45ab", "533c0cba763f4a505e8b46db" ] - multiple id's
52c2ff91763f4a572d8b45e6 - single id

I'm using COUNTIF to count all the instances of a given id. if there were only a single id in each cell, it would work. But, when there are more than one id in a cell it fails.

How do I count the number of times a given instance shows up in the column? Happy to use excel functions or VBA.

thanks

Upvotes: 0

Views: 94

Answers (2)

Alex P
Alex P

Reputation: 12497

I like Gary's Student approach for ease.

For reference here is a VBA solution using a user-defined function. Example:

    A                                                       B
1   52c2ff91763f4a572d8b45e6                                `=COUNTIDS(A1)` = 4
2   52c2ff91763f4a572d8b45e6, 52c2ff91763f4a572d8b45e6
3   52c2ff91763f4a572d8b45e6
4   52b46bfc763f4ad9198b45ab, 533c0cba763f4a505e8b46db

This code inside a VBA module:

Function CountIDs(id As Range)
    Dim ids As Range, cl As Range, cnt As Long, arr As Variant, i As Integer

    Set ids = Range("A1:A" & Range("A1").End(xlDown).Row) //amend to suit your set-up
    cnt = 0

    For Each cl In ids
        If InStr(1, cl, ",", vbTextCompare) Then
            arr = Split(cl, ",")

            For i = 0 To UBound(arr)
                If VBA.Trim(arr(i)) = id Then
                    cnt = cnt + 1
                End If
            Next i

        Else
            If cl = id Then
                cnt = cnt + 1
            End If
        End If
    Next

    CountIDs = cnt
End Function

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96781

use wildcards: 

=COUNTIF(A:A,"*52c2ff91763f4a572d8b45e6*")

for example:

COUNTIF

Upvotes: 4

Related Questions