Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count occurances of text in a cell
Good day,
I have a sheet with numerous text strings all in the same column. I need to search each and return the number of occurances (total) of the words "medicare" and "eomb" cointained in each string. I've tried this function: =COUNTIF(A1:A10,"*medicare*")+COUNTIF(A1:A10,"*eom b*") but it only counts 1 occurance of each. I need to the total times the word 'medicare occurs plus the total times 'eomb' occurs. Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count occurances of text in a cell
Hi,
Your formula should work - I tried it on my version (Excel 2002 SP2) and it was OK, it also didn't matter if the words in the sheet were upper or lower case. I know it doesn't solve your problem, but at least you know it should work! I looked in options and other places to try and find anything that might cause it to fail but no luck. Perhaps someone else can shed some light on it. Cheers Nigel "JayL." wrote in message news:r9kob.64804$Fm2.52722@attbi_s04... Good day, I have a sheet with numerous text strings all in the same column. I need to search each and return the number of occurances (total) of the words "medicare" and "eomb" cointained in each string. I've tried this function: =COUNTIF(A1:A10,"*medicare*")+COUNTIF(A1:A10,"*eom b*") but it only counts 1 occurance of each. I need to the total times the word 'medicare occurs plus the total times 'eomb' occurs. Any suggestions? ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count occurances of text in a cell
Good day, Jay
I'm not sure, what you are after. Is it *words* ( can exist at 3 places: at the start of the string, inside the string and at the end of the string) At the start, it's the characters followed by a space, inside the string it's a space followed by the characters followed by a space, and at the end it's a space followed by the characters. OR Are you looking for the *characters* no matter where they pop up (inside another word)? You say *words*, but your formula indicates *characters*, so here is a solution for either: For *words*, assuming the word to count for is entered in F1 =SUMPRODUCT((LEN(" " &A1:A10&" ")-LEN(SUBSTITUTE(" "&A1:A10&" "," "&F1&" ","")))/(LEN(F1)+2)) for *characters* (again entered in F1) =SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,F1,"")))/LEN(F1)) -- Best Regards Leo Heuser Excel MVP Followup to newsgroup only please. "JayL." skrev i en meddelelse news:r9kob.64804$Fm2.52722@attbi_s04... Good day, I have a sheet with numerous text strings all in the same column. I need to search each and return the number of occurances (total) of the words "medicare" and "eomb" cointained in each string. I've tried this function: =COUNTIF(A1:A10,"*medicare*")+COUNTIF(A1:A10,"*eom b*") but it only counts 1 occurance of each. I need to the total times the word 'medicare occurs plus the total times 'eomb' occurs. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Occurances | Excel Discussion (Misc queries) | |||
Count the number of occurances in a cell | Excel Worksheet Functions | |||
Using COUNT for text occurances in unusual worksheet? | Excel Worksheet Functions | |||
substituting two text occurances in same cell. | Excel Worksheet Functions | |||
counting occurances of a char. within a text string/cell | Excel Programming |