Want to count every occurrence of a string within ONE CELL

[91709jack]

HI all,

Here's what the string would look like (very simplified version):

1701191000m0 1612021227n3 1611111356f0 1609251007c0 1608141700f0 1512251000n0

I would want to know the TOTAL number of times "16???? " shows up, which would be 4.

The functions I've tried so far only count ONE instance, so it would only =1.

Stumped! Appreciate your help.

THANKS!

Jack

 

[91709jack]

Hi Rory,

THANK YOU!!!!! With wildcards & all! IT WORKS!!!! THANK YOU!

Now one other question: I'm doing this across 15,000 rows. Any elegant way of speeding it up or simplifying? THANKS AGAIN.

 

[StephenCrump]

=COUNT(SEARCH(B1,MID(" "&A1,IF(MID(" "&A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ",ROW(INDIRECT("1:"&LEN(A1)))+1),LEN(B1))))

Nice!

1701181532x3 16thank* 1611071617n1 16prevm* mbe wot 1610031111x1 16jtkids* 1608161802n2 16cpros* 1606131717n1 16p60l* 1604181432n1 16q5* 1602231706n2 16ja* 1510031816f6 q4* mayor irvine knock 22k doors 1507081544n3 q3v2* 1504281111f2 vvn sd sell q2x11b 1501211515x3 omyr 1408181933n0 a4* cbnn not selling yet 1310121616n0 dog

how many substrings look like this: "16????1????? "

should be 6 (in BLUE)

... but won't a search on 16????1????? return 8, including these two:

16q5* 16022
16ja* 15100

 

[91709jack]

Hi Stephen, no it won't. You missed a little something. "16????1????? " - there's a SPACE after the last question mark wildcard.

 

[StephenCrump]

You missed a little something. "16????1????? " - there's a SPACE after the last question mark wildcard.

Ahh! My mistake, sorry. Rory's formula looks good.

 

[RoryA]

I don't know if this UDF would be faster:

Function Countem(InputText As String, Pattern As String) As Long
    Dim vTemp
    Dim n As Long
    Dim patternLength As Long
    
    InputText = VBA.UCase$(InputText)
    Pattern = VBA.UCase$(Pattern)
    
    patternLength = Len(Pattern)
    
    vTemp = Split(InputText, " ")
    
    For n = LBound(vTemp) To UBound(vTemp)
        If Len(vTemp(n)) >= patternLength Then
            If vTemp(n) Like Pattern Then Countem = Countem + 1
        End If
    Next n
End Function

Note: it splits on spaces, so don't include the space on the end of the search pattern.

 

[Rick Rothstein]

I don't know if this UDF would be faster:

Function Countem(InputText As String, Pattern As String) As Long
    Dim vTemp
    Dim n As Long
    Dim patternLength As Long
    
    InputText = VBA.UCase$(InputText)
    Pattern = VBA.UCase$(Pattern)
    
    patternLength = Len(Pattern)
    
    vTemp = Split(InputText, " ")
    
    For n = LBound(vTemp) To UBound(vTemp)
        If Len(vTemp(n)) >= patternLength Then
            If vTemp(n) Like Pattern Then Countem = Countem + 1
        End If
    Next n
End Function

Note: it splits on spaces, so don't include the space on the end of the search pattern.

If I am not mistaken, I believe your code can be "simplified" to this...

Function Countem(InputText As String, Pattern As String) As Long
    Dim vTemp
    Dim n As Long
    
    InputText = VBA.UCase$(Application.Trim(InputText))
    Pattern = VBA.UCase$(Pattern)
    
    vTemp = Split(InputText, " ", , vbTextCompare)
    
    For n = 0 To UBound(vTemp)
        If vTemp(n) Like Pattern Then Countem = Countem + 1
    Next n
End Function

Note: I changed the lower bound in the loop to zero because the Split function always produces a zero-based array even if Option Base 1 is in use.

 

[RoryA]

I don't know how efficient Like is, which is why I added the length test. I also don't like hardcoding array bounds ever. I'd be surprised if it makes any real difference to be honest.

 

[Rick Rothstein]

I don't know how efficient Like is, which is why I added the length test.

Ah, okay, I see... however, that test might not be advisable if the pattern included an asterisk wildcard, correct?

I also don't like hardcoding array bounds ever. I'd be surprised if it makes any real difference to be honest.

Normally, I would agree with you, but in the case of Split, LBound will always evaluate to zero... always using that is sort of like always using one for the lower bound of a Variant array created from a range since it will always be a value of one (I do not use LBound for that either). By the way, I think you are right about there being no speed difference (well, there is one, but it is miniscule) as the limits for a For..Next loop are only calculated once.

 

[RoryA]

Ah, okay, I see... however, that test might not be advisable if the pattern included an asterisk wildcard, correct?

That's true - I should have added that as a caveat.