Count number of occurrences of value in string

[DougStroud]

I have a series of strings that contain colons. I would like to count the number of occurrences of the colon.

in AB10=
Accessories : Accessories : Stickers & Key Chains

Formula yields 1, but looking to return 2.
=COUNT(FIND(":",AB10))

 

[Peter_SSs]

Doug

Try this:
=LEN(AB10)-LEN(SUBSTITUTE(AB10,":",""))

 

[ADAMC]

=LEN(AB10)-LEN(SUBSTITUTE(SUBSTITUTE(AB10,":",""),":",""))

 

[DougStroud]

Hey Peter,
That works, Bravo!

Thank you.

 

[DougStroud]

Thanks Adamc,
Your formulae works too, but the second substitue function is not required.

cheers

 

[ADAMC]

Yep, i realised that as soon as i posted it, it was taken from a formula i already had in one of my workbooks.

All the best

 

[DougStroud]

Yep...., no worries.

thanks again