count Re-occurrences

**starguy** · 10-06-2006, 05:48 AM

Originally Posted by stevekirk

hi,

if i have "fred" in a cell 5 times how do i get the next cell to tell me 5

thanks
steve

if you have "fred" entered in a single cell with a space among each like this:

A1= fred fred fred fred fred

then put in B1

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

**stevekirk** · 10-06-2006, 06:21 AM

hi,

sorry i have confused you

the text in the cell could be "egbd/saw/egbd/dis" or "egbd/saw/dis/saw"

i want to know how many times "egbd" is in that cell or then in the next cell
how many times saw in in it

sorry for the confussion

steve

**Bryan Hessey** · 10-06-2006, 07:06 AM

Originally Posted by stevekirk

hi,

sorry i have confused you

the text in the cell could be "egbd/saw/egbd/dis" or "egbd/saw/dis/saw"

i want to know how many times "egbd" is in that cell or then in the next cell
how many times saw in in it

sorry for the confussion

steve

Either

=(LEN(A1)-LEN(SUBSTITUTE(A1,"saw","")))/3+((LEN(B1)-LEN(SUBSTITUTE(B1,"saw","")))/3)

or

=""&(LEN(A1)-LEN(SUBSTITUTE(A1,"saw","")))/3&" and "&((LEN(B1)-LEN(SUBSTITUTE(B1,"saw","")))/3)

etc

---

**stevekirk** · 10-06-2006, 07:33 AM

hi bryan,

i thought i knew my way around your formulars but i guess not

please see attached file for example

the results i need are in red in ak2

steve

**Bryan Hessey** · 10-06-2006, 07:47 AM

Originally Posted by stevekirk

hi bryan,

i thought i knew my way around your formulars but i guess not

please see attached file for example

the results i need are in red in ak2

steve

I used $Ai for the second, but it's not the correct cell.

Was there only AH to total? - if so rip out all after the + in the middle.

btw, the formula (as now) drags sideways AND downwards - still works

and don't put spaces after the words in row 1, otherwise "dis " won't match.

Cheers

**stevekirk** · 10-06-2006, 08:11 AM

bryan,

i have attached a file with all the furmulars in that you have given me before

please see why it did not for work for me and adjust please

steve

**Bryan Hessey** · 10-06-2006, 08:10 PM

Originally Posted by stevekirk

bryan,

i have attached a file with all the furmulars in that you have given me before

please see why it did not for work for me and adjust please

steve

A quick look (will be back in 2 days) suggests

AL1
dis = beam saw <<< you cannot search on this
AL2
=(LEN($AI2)-LEN(SUBSTITUTE($AI2,AL$1,"")))/LEN(AL$1)+((LEN($AJ2)-LEN(SUBSTITUTE($AJ2,AL$1,"")))/LEN(AL$1))
should be:
=(LEN(AI2)-LEN(SUBSTITUTE(AI2,"dis","")))/3+((LEN(AI2)-LEN(SUBSTITUTE(AI2,"saw","")))/3)

AM1
edge <<<< if 'edge' is 'egbd'
AM2
=(LEN($AI2)-LEN(SUBSTITUTE($AI2,AM$1,"")))/LEN(AM$1)+((LEN($AJ2)-LEN(SUBSTITUTE($AJ2,AM$1,"")))/LEN(AM$1))
should be:
=(LEN(AI2)-LEN(SUBSTITUTE(AI2,"egbd","")))/4

AN1
biesse
AN2
=(LEN(AI2)-LEN(SUBSTITUTE(AI2,AN$1,"")))/LEN(AN$1)

AO1
alt
AO2
=(LEN(AI2)-LEN(SUBSTITUTE(AI2,AO$1,"")))/LEN(AO$1)

AP1 ............ I presume you want to count 'bi'
bi
AP2
=(LEN(SUBSTITUTE(AI2,"biesse",""))-LEN(SUBSTITUTE(SUBSTITUTE(AI2,"biesse",""),"bi","")))/2

E2
_________________2L/2S
use
2L/2S
and align to right for your display.

AI2 is 30 always enough?
suggest 50
(I blew 30 in testing)

cheers
---

**starguy** · 10-06-2006, 07:26 AM

Originally Posted by stevekirk

hi,

sorry i have confused you

the text in the cell could be "egbd/saw/egbd/dis" or "egbd/saw/dis/saw"

i want to know how many times "egbd" is in that cell or then in the next cell
how many times saw in in it

sorry for the confussion

steve

try this

=(LEN(A1)-LEN(SUBSTITUTE(A1,"egbd","")))/4

you need to manually enter a digit at the end of formula (4 above formula)
this infact is length of text you want to count in a cell.
here is "egbd" has four characters so you need to enter it at the end of formula.

Regards.