Finding text in a cell
 

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?

Finding text in a cell
 

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

art wrote:

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?

--

Dave Peterson

Finding text in a cell
 

One way:

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

In article ,
art wrote:

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?

Finding text in a cell
 

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.


"JE McGimpsey" wrote:

One way:

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

In article ,
art wrote:

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?

Finding text in a cell
 

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



art wrote:

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.

"JE McGimpsey" wrote:

One way:

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

In article ,
art wrote:

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?


--

Dave Peterson

Finding text in a cell
 

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

"Dave Peterson" wrote:

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



art wrote:

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.

"JE McGimpsey" wrote:

One way:

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

In article ,
art wrote:

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?


--

Dave Peterson

Finding text in a cell
 

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



art wrote:

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

"Dave Peterson" wrote:

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



art wrote:

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.

"JE McGimpsey" wrote:

One way:

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

In article ,
art wrote:

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?


--

Dave Peterson


--

Dave Peterson

Finding text in a cell
 

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

"Dave Peterson" wrote:

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



art wrote:

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

"Dave Peterson" wrote:

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



art wrote:

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.

"JE McGimpsey" wrote:

One way:

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

In article ,
art wrote:

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?


--

Dave Peterson


--

Dave Peterson

Finding text in a cell
 

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.



art wrote:

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

"Dave Peterson" wrote:

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



art wrote:

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

"Dave Peterson" wrote:

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



art wrote:

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.

"JE McGimpsey" wrote:

One way:

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

In article ,
art wrote:

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?


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson