Finding text in a cell

A

art

Which formula do I have to use to see how many times a certain text is in a
cell. For Example, in cell A1 is the text "bread milk bread", how can I in a
different cell see how many times the word bread is in cell A1?
 
A

art

Thanks to Dave Peterson and Je McGimpsey, it works great, thanks a lot. I saw
basically the same answer from Dave Peterson in old question you aswered a
while ago. Thanks.
 
D

Dave Peterson

Just a small warning.

If you want to count Bread, BRead, BREad, ..., bread as all the same string, you
could use:

=(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"bread","")))/LEN("bread")

=substitute() is case sensitive.

And the first suggested formulas will only look for lower case "bread".
 
A

art

How can I find how many times the word bread is in a whole range of cells,
say from A1:A10?
 
D

Dave Peterson

If you want to count the number of cells that contain bread:

=countif(a1:a10,"*bread*")

If you want to count the number of times that bread appears in A1:A10 (counts
multiple occurences within the each cell):

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),"bread","")))/LEN("bread")
 
D

Dave Peterson

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach has a few books in different categories (formulas and
programming). But his books are very easy to read. You may want to print that
list and visit your local bookstore/library to see which one fits best for you.


Thanks, Your great. Any book you would recommend to read bout excel?
Click to expand...
 
Top