Count the Occurrences with Find

[MarkCBB]

Hi there,

I need some assistance to change this formula so that duplicates in a text string are counted.


Example:
Text String: 1234,1234 + 1212 (this is in Cell A10)
Formula: {=IF(IFERROR(FIND(D$1,$A10),"")<>"",1,0)} (this is in D10)
D1: contains the "1234"

Currently this formula only brings back "1", I need a new formula that will see that there are 2 Occurrences in the text, resulting in a "2"

 

[Colin Legg]

Hi,

Try this:

=(LEN(A10)-LEN(SUBSTITUTE(A10,D1,"")))/LEN(D1)

 

[Aladin Akyurek]

Hi there,

I need some assistance to change this formula so that duplicates in a text string are counted.


Example:
Text String: 1234,1234 + 1212 (this is in Cell A10)
Formula: {=IF(IFERROR(FIND(D$1,$A10),"")<>"",1,0)} (this is in D10)
D1: contains the "1234"

Currently this formula only brings back "1", I need a new formula that will see that there are 2 Occurrences in the text, resulting in a "2"

Try...

=(LEN(A10)-LEN(SUBSTITUTE(A10,D1,"")))/LEN(D1)

 

[MarkCBB]

Thanks Colin and Aladin,

The formula works perfectly, thanks for your assistance.