Counting within a cell
 

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

Counting within a cell
 

On Mon, 27 Oct 2008 09:11:03 -0700, cellcounting
wrote:

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

Counting within a cell
 

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 ")

--
Biff
Microsoft Excel MVP


"cellcounting" wrote in message
...
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

Counting within a cell
 

On Mon, 27 Oct 2008 13:06:20 -0400, "T. Valko" wrote:

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

Counting within a cell
 

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

--
Biff
Microsoft Excel MVP


"Ron Rosenfeld" wrote in message
...
On Mon, 27 Oct 2008 13:06:20 -0400, "T. Valko"
wrote:

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