Find no. of repetitions of a string in a cell

[Hitesh_Banda]

Hi,

I have a text in cell H2.

My sheet contains text values from A5 to A233. I need to find out how many times the text in cell H2 is being repeated in the column A. (Apparently the whole sheet excluding H2)

Is there any formula where I can find out the no. of instances of an instring in a cell so that I can sum them all and get the total no. of repetitions?

Thanks.

 

[mancubus]

if cell values (cell contents) are exactly the same:

=COUNTIF(A5:A233,H2)


edit: i did not see "instring". sorry.

 

[Dannyh1]

Hi

How about:

In another column (col B perhaps)

=IF(ISNA(MATCH($H$2,A1,0)),"",1) - copy down

Then SUM column B

 

[Darren Bartrup]

When you say 'instring' I take it you mean the text may appear within other text...

e.g. Searching for the string 'Text' you could have 'aaaTextaaa', 'Text', 'SomeText', 'dsafa'

You can use wildcards in the COUNTIF function to find all three instances in the example above:

=COUNTIF(A5:A233,"*" & $H$2 & "*")

This will only find 1 instance per cell though - 'aaaaTEXTaaaaTEXTaaa' will only count as 1.

 

[Hitesh_Banda]

Sorry I was not clear when posting this thread. The text in cell H2 is going to repeat more than once in some cells. I need to find the no. of those instances.

 

[Dannyh1]

nice solution Delmar

 

[Dannyh1]

Is there a maximum number of times it can occur in one cell?

 

[Hitesh_Banda]

Maximum no of times it is going to repeat is 5.

 

[Darren Bartrup]

Possibly a custom function to count them all, but if there's a maximum of 5 then could do a formula I guess... but here's the custom function version:

Function CountInString(Target As Range, SearchFor As String) As Long

    Dim lAns As Long
    Dim sText As String
    Dim rngCell As Range
    
    For Each rngCell In Target
        sText = sText & rngCell.Value
    Next rngCell
    
    Do While InStr(sText, SearchFor) > 0
        lAns = lAns + 1
        sText = Replace(sText, SearchFor, "", 1, 1)
    Loop

    CountInString = lAns

End Function

 

[Mile]

=SUM(--(MID(A5,ROW(INDIRECT("1:"&LEN(A5))),LEN(H2))=H2))

Arrayformula - Confirm with CTRL + SHIFT + ENTER (and copy down to A233)


... but I don´t see how to SUM them directly in a resultcell.

/Mile