| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct issues | Excel Worksheet Functions | |||
| Text entries behaving like numbers | Excel Discussion (Misc queries) | |||
| Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
| Formulas dealing with text data | Excel Worksheet Functions | |||
| Possible Lookup Table | Excel Worksheet Functions | |||
Linear Mode
