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? |
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 =--- |
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? |
All times are GMT +1. The time now is 06:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com