LinkBack URL
About LinkBacks
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
---
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.
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.
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
---
Last edited by Bryan Hessey; 10-06-2006 at 08:12 PM.
hi, bryan
hope you had a good break
the 2l, 2l/2s in column e MUST ALWAYS be the last 5 charecters in the cell
this is because the field in the main frame has only 23 spaces
therefore ak has right(e2,5) this then unfortuatly leave spaces in front of the ones that say 2L OR 2S then your other formulars dont work unless i remove the spaces manaullly
can we put in another formular to overcome this
thanks
steve
One of two options, you can use Trim(A1) on any cell that has leading or trailing spaces to get the valid data, or, if you are trying to access 5 characters from A1 and it is only 1 character long use A1&" " etc
=Right(" "&A1,5)
or
=Left(A1&" ",5)
etc.
so right("_____"&E2,5)
should work for you. (with 5 spaces)
hth
---
Last edited by Bryan Hessey; 10-10-2006 at 08:22 AM.
bryan,
thanks
what worked was (trim(right(e2,5)
hopefully that project completed ready for the next
thanks for all your help
i have learnt a lot
steve
Great to see, and good that the whole project is now complete (that was some exercise).
Good luck with your next.
---
hi bryan,
project not completed
i need to be able to put a value in ai 2 depending on the size of the material i am using and if the word biesse is in the routing
the costs for the biesse are in the "cost" sheet and also the plus up depending on the material size
please see attached file
thanks
steve
Steve,
The formula for AI2 is
=AC2*Sheet2!$M$2*IF(AC2<>1,1,VLOOKUP(V2,costs!A$22:B$27,2,TRUE))
but you need to set the Lookup table as per costs!A22:B27
You can put this somewhere as a Named range, or just refer to the cells directly as shown.
I presume that you didn't want costs!e14 changed, but wanted to apply this row-by-row in Sheet1.
Cheers
----
thanks bryan
works great
steve
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks