Counting within a cell

[cellcounting]

Need a function that can count how many times the number 3614 is within a
single cell.
examble value from cell "3614 ref lead 3614 ref go 3614"= 3

 

[Ron Rosenfeld]

Need a function that can count how many times the number 3614 is within a
single cell.
examble value from cell "3614 ref lead 3614 ref go 3614"= 3

=(LEN(A1)-LEN(SUBSTITUTE(A1,"3614","")))/LEN("3614")

Obviously, you could put 3614 in some cell, and substitute that cell reference
in the above.
--ron

 

[T. Valko]

Try one of these:

Based on your sample...

=(LEN(A1)-LEN(SUBSTITUTE(A1,3614,"")))/LEN(3614)

However, that will return an incorrect result if the entry was something
like this:

36142 ref lead 3614 ref go 3614

This version is more robust but is still not "bullet-proof" and assumes that
there are spaces before/after the substring of interest:

=(LEN(" "&A1&" ")-LEN(SUBSTITUTE(" "&
A1&" "," 3614 ","")))/LEN(" 3614 ")

 

[Ron Rosenfeld]

However, that will return an incorrect result if the entry was something
like this:

36142 ref lead 3614 ref go 3614

bbbbut 3614 is contained within 36142
--ron

 

[T. Valko]

It took me 10 minutes to decide whether or not to include that caveat and
the other formula!