LinkBack URL
About LinkBacks
stevekirk 
hi,
if i have "fred" in a cell 5 times how do i get the next cell to tell me 5
thanks
steve
hi,
if i have "fred" in a cell 5 times how do i get the next cell to tell me 5
thanks
steve
Originally Posted by stevekirk
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
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
---
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
Last edited by Bryan Hessey; 10-06-2006 at 07:52 AM.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks