Count the frequency of a specific word in a single cell

excel count how many times a word appears in a range
excel count how many times a word appears in a cell
how to count specific words in excel
excel count number of occurrences of each value in a column
excel count number of occurrences in a column
how to count specific words in excel column
count word frequency in excel
excel count occurrences of string in cell

In Microsoft Excel I wish to count the frequency of a specific word in a cell. The cell contains a few sentences. I am using a formula right now that is working, but not the way I want it to.

A1

my uncle ate potatos. potato was his favorite food. Don't mash the potato, just keep it simple.

B1 (word to count the frequency of)

potato

C1 (forumula)

=(LEN(A2)-LEN(SUBSTITUTE(A2;B2;"")))/LEN(B2)

C1 Results:

3

In C1, I am getting a count 3. I want it just to be 2. So, the formula is counting potatos.

How do I make the function only count exact matches?

I do have an alternate solution for you to consider. I takes a bit more space and the formulas are a little more convoluted, but in some senses it will be simpler.

Use column C as a new helper column. Column C will take the text from column A, and will substitute out all instances of punctuation with a " ". Once this has been done, the formula to count the instances of the search term from column B will be a simple formula essentially as you have it in your OP.

 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"."," "),";"," "),"-"," "),"/"," ")

This formula first substitutes all slashes for spaces, then with that substituted text it substitutes dashes for spaces, then with that substited text it substitutes semicolons with spaces, etc. As you indicated, if you use semi-colons as delimiters, you will need to replace my commas separating terms with semi-colons.

Then the formula in D1 is simply what you have above in your OP, with two changes: we will be searching for B1 & " ", because we know all of the 'exact matches' now end in spaces, and we will be adding in an extra '1' if the last part of the text in C1 is the same as the search term in B1 - because if a cell ends in that word, it won't have a space, but it is still an 'exact match'. Like so:

=(LEN(C1)-LEN(SUBSTITUTE(C1,B1&" ","")))/(LEN(B1)+1)+IF(RIGHT(C1,LEN(B1))=B1,1,0)

EDIT

My list of punctuation was only a suggestion; I recommend you really go through some sample text and make sure you don't have any weird characters after words. Also, consider changing uncommon ones I have (like "/", or "-") with "?" or "!". If you want to add more, just follow the pattern of the SUBSTITUTE formula.

To make this case-insensitive, you just need to change the formula in column C to make the result all lower case, and then ensure your search terms in column B are lower case. Change column C like so:

 =LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"."," "),";"," "),"-"," "),"/"," "))

How to count frequency of a text/number/character in Excel column?, If you want to count the frequency of a value in a range or a cell, you can try to to count a specific word in Text textbox and type the word into the Word textbox. a word appears function, you can count the frequency of a word in a single cell. Count number of certain words in a cell with formula. You can combine the LEN and SUBSTITUTE functions to count number of specific word in a cell in Excel. =(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word) 1. Select a blank cell, enter the below formula into it and press the Enter key. =(LEN(B2)-LEN(SUBSTITUTE(B2,D2,"")))/LEN(D2) Notes:

I've got a solution here but it's not pretty.

The problem, as I indicate in my comment, is that Excel has no internal function to see if a cell contains an 'exact match'. You can check if the total value in a cell is an exact match, but you can't check whether a search term has been conjugated like that. So, we'll need to create a special method which checks for every 'acceptable' ending to a word. In my eyes, this would be anything that ends with space, anything that ends with punctuation, and anything at the end of a cell with nothing after it.

ARRAY FORMULAS

You were on the right track with the LEN - SUBSTITUTE method, but the formula will need to be an array formula to work. Array formulas calculate the same thing multiple times over a given range of cells, instead of just once. They resolve the calculation for each individual cell in a formula and provide an array of results. This array of results must be collapsed together to get a single total result.

Consider as follows:

=LEN(C1:C6)

Confirm this formula with CTRL + SHIFT + ENTER instead of just ENTER. This gives us the LEN of C1, followed by C2, C3... etc., resulting in an array of results that looks like this [assume C1 had "a", C2 had "aa", C3 had "a", C4 had "", C5 had "aaa", and C6 had ""]:

={1;2;1;0;3;0}

To get that as a single number providing the total length of each cell individually, wrap that in a SUM function: =SUM(LEN(C1:C6))

Confirmed again with CTRL + SHIFT + ENTER instead of just ENTER. This results in the total length of all cells: 7.

DEFINING AN EXACT MATCH

Now to take your question, you are looking to find all 'acceptable' matches of given word B1, within text A1. As I said before, we can define an acceptable answer as one which ends in punctuation, a space, or the end of the cell. Something at the end of the cell is a special case which we will consider later. First, take a look at the formula below. In cells C1:C6, I have manually typed a comma, a period, a semi-colon; a hyphen, a space, and a slash. These will be the 'acceptable' ways to end the word found in B1.

=LEN(SUBSTITUTE(A1,B1&C1:C6,""))

Confirmed with CTRL + SHIFT + ENTER, this takes the length of the substitution for the search term in B1 appended with the acceptable word-end in C1:C6. So it gives the length for 6 new SUBSTITUTED words. But as this is an array of results, we need to add them together to get a single number, like so:

=SUM(LEN(SUBSTITUTE(A1,B1&C1:C6,"")))

FORMULIZING THE RESULT

To work it as you have in your sentence, we will now need to subtract this length from the length of the original word. Note that there is a problem with doing this simply - since we are searching multiple times, we will need to add the length of the original word multiple times. Consider something like this:

=LEN(A1)-SUM(LEN(SUBSTITUTE(A1,B1&C1:C6,"")))

This won't work, because it only adds the length of A1 once, but it subtracts the length of the substituted strings multiple times. How about this?

=LEN(A1)*6-SUM(LEN(SUBSTITUTE(A1,B1&C1:C6,"")))

This works, because there are 6 word-end terms we search for with C1:C6, so the substitution there will occur 6 times. So we have the original length of the word 6 times, and the length of each substituted word 6 times [keep in mind that if there is no match for, say, "potato;", then that term will give the length of the original word, thus negating one of the times we added the length of that word, as expected].

To finalize this, we need to divide by the number of letters in the search term. Keep in mind that where you have "/LEN(B1)", we will need to add a character for the length of each of our word-ends.

=(LEN(A1)*6-SUM(LEN(SUBSTITUTE(A1,B1&C1:C6,""))))/(LEN(B1)+1)

Finally, we need to add the special case where the last portion of A1 is equal to the search term, with no word-end. Alone, this would be:

=IF(RIGHT(A1,LEN(B1))=B1,1,0)

This will give us a 1 if the last part of A1 is equal to B1, otherwise it gives 0. So now simply add this to our previous formula, as follows:

=(LEN(A1)*6-SUM(LEN(SUBSTITUTE(A1,B1&C1:C6,""))))/(LEN(B1)+1)+IF(RIGHT(A1,LEN(B1))=B1,1,0)

Remember to confirm with CTRL + SHIFT + ENTER, instead of just ENTER. That's it, it now gives you the count of all "exact matches" of your search term.

ALTERNATE APPROACH TO ARRAY FORMULAS

Note that instead of using C1:C6, you could instead hardcode your formula to look for specific punctuation as the word-end. This will be harder to maintain but, in my opinion, just as readable. It will look like this:

=(LEN(A1)*6-SUM(LEN(SUBSTITUTE(A1,B1&{",",".",";"," ","/","-"},""))))/(LEN(B1)+1)+IF(RIGHT(A1,LEN(B1))=B1,1,0)

This is still technically an "array formula", and it works on the same principles as I have described above. However, one benefit here is that you can confirm this type of entry with just ENTER. This is good, in case someone accidentally edits your cell and presses ENTER without noticing. Otherwise, this is equivilent to the format above.

Let me know if you would like any portion of this elaborated on.

Excel formula: Count specific words in a cell, If you need to count how many times a specific a word (or any substring) In the generic form of the formula above, a1 represents a cell that contains text, and One workaround is to use another formula in a helper column to "normalize text" � In Microsoft Excel I wish to count the frequency of a specific word in a cell. The cell contains a few sentences. I am using a formula right now that is working, but not the way I want it to. A1. my uncle ate potatos. potato was his favorite food. Don't mash the potato, just keep it simple. B1 (word to count the frequency of) potato C1 (forumula)

Sorry for making it "a new answer". You may move it wherever you want.

I have just found a solution for the answer Liu Kang asked on Aug 3 2015 at 12:15. :) Unfortunately, I do not have "50 reputation" to comment on Grade 'Eh' Bacon's solution above, where the last comment is this:

Discovered a slight problem. Using =IF(B1<>"";(LEN(A1)-LEN(SUBSTITUTE(A1;B1&" ";"")))/(LEN(B1)+1)+IF(RIGHT(A1;LEN(B1))=B1;1;0);"") with shoe in B1 gives the following result: shoe in A1 = 1 (correct), shoes in A1 = 0 (correct), ladyshoe in A1 = 1 (wrong). Guess this have to do with "RIGHT" in the formula. Is it possible to make the formula non-matching for prefix words? E.g if B1 is containing shoe and A1 is containing ladyshoe dogshoe catshoes shoes I want C1 to result in 0. – Liu Kang Aug 3 '15 at 12:15

The solution is to search for a space at the beginning of the word as well (" "&B1&" ") and to add "one" more LEN(B1)+2. So, it becomes =IF(B1<>"";(LEN(A1)-LEN(SUBSTITUTE(A1;" "&B1&" ";"")))/(LEN(B1)+2)+IF(RIGHT(A1;LEN(B1))=B1;1;0);"").

There is one more problem if the word we are looking for is at the beginning. Because there is obviously no space " " at the beginning of the sentence. I use a workaround for it - I have my sentence in A1, but then I have a hidden column B where there is =" "&A1 in B1 and it puts the "space" I need to the beginning of the sentence and everything from the original Grade 'Eh' Bacon's solution is shifted (A1->B1, B1->C1, C1->D1).

I hope it can help and thanks to all who participated in this thread, you helped me A LOT!

Excel formula: Count specific words in a range, To count how many times a specific a word (or any substring) appears inside a range of cells, you can use a formula based on the SUBSTITUTE, LEN, and� Exact frequency of a specific word in a single cell (excluding suffix and prefix) Ask Question Count the frequency of a specific word in a single cell. 0.

Do you need this to be a single formula? I have an idea, but it takes a few (relaitvely simple) steps.

Since you have a long sentence in A1, what about going to Data -> Text to Columns, and send this sentence into a Row, delimited by spaces. Then, remove any punctuation. Then, just do a simple Countif()?

Put the info in A1, then go to Data --> Text to Columns, choose "Delimited", click Next, and choose "Space":

Click Finish, and it'll put the entire thing into Row 1, with a word in each cell. Now just Find/Replace "." and "," with nothing.

Then, Countif to the rescue!

If that works, we can automate into VB, so you don't have to manually find/replace the puncutation. Before I jump into that, does this method work?

Formulas to count the occurrences of text, characters, and words in , The number of occurrences of a character in one cell. range in question and " text" is replaced by the specific text string that you want to count. If you need to count how many times a specific a word (or any substring) appears inside a cell, you can use a formula that uses SUBSTITUTE and LEN. In the generic form of the formula above, a1 represents a cell that contains text, and txt represents the word or substring being counted. In our example, we are using this formula:

Take the length of the string and minus the length of the string with the keyword replaced with nothing then divide the result by the length of the keyword:

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

How to count word frequency in a cell range [UDF], How to count word frequency in a cell range [UDF] This user defined function creates a unique distinct list of words and how many i just need to count the how many times FDSB occur in one perticular sheet My requirement is to copy data from ms excel and paste into ms word on specific position If you want to count the number of a specific word in a single cell, you need to use the SUBSTITUTE function to remove all that certain word in text string, then using LEN function to calculate the length of the substring that without that specific word.

How to Count Occurrences of a Word in an Excel Range in Excel, This excel formula counts occurrences of word in excel range. We have already learnt how to count cells that contain a specific text using COUNTIF function. How to use the VLOOKUP Function in Excel: This is one of the most used and� Count specific words. In order to count specific words occurring in a cell, we follow these steps: Step 1. Select cell D4. Step 2. Enter the formula: =(LEN (B4)-LEN (SUBSTITUTE (B4,C4,"")))/ LEN (C4) Step 3. Press ENTER. Step 4. Copy the formula in cell D4 to cells D5:D7 by clicking the “+” icon at the bottom-right corner of cell D4 and dragging it down. Figure 3. Count specific words using LEN and SUBSTITUTE

Counting Specific Words with COUNTIF, Using Excel's COUNTIF Function to Count Specific Words the frequency of a glossary of words in text, but wasn't sure how to go about it. If we use a link to another cell ("=*"&C2&"*") that contains a word, we can make one formula work for� If you just want to count cells that contain a specific word, see this simple formula based on the COUNTIF function. How this formula works In the generic version of the formula, rng represents the range to check, and txt is the word or substring to count.

How can I calculate the frequency of specific words for each row in , How can I calculate the frequency of specific words for each row in the a data. frame, is each sentence stored in a single cell, or every word? Count how often a single value occurs by using the COUNTIF function. Use the COUNTIF function to count how many times a particular value appears in a range of cells. For more information, see COUNTIF function.

Comments
  • First, I'll point out that the Sumproduct portion of your formula does not appear to be doing anything, so that can be removed and will simplify how things look somewhat. As for how to solve your issue, there is nothing set in Excel to recognize 'whole words within a cell' like that as a usable function, so you will need to do some tweaking. Could you clarify what sorts of words will be appearing? Could be as simple as adding "-count for those which end in s" to end of your formula.
  • I removed Sumproduct, thanks for the feedback. I will analyse large portions of texts. For example - a text about dogs. The words the text includes is dog, doge, dogs, doglike, dogface and dogteeh. And I want to count just how many times dog is mentioned in the text.
  • Have you looked at my answer below? It should do what you want. Let me know if you have questions about how it works.
  • Thanks so much for taking the time. Changed the commas to semi-colons. The formula in C1 is working, punctuation is removed. But in D1 I am getting 4,375, using the same content in A1 as my orgininal post. Will this solution be working if I have many rows? E.g in A1 I have a text about potatos and in A2 I have a text about dogs.
  • Also - you will need to do this formula for each row - you can drag this formula down as many times as you want, as long as column A always includes the 'searched' text, column B always includes the 'search term', and column C always includes the substituted search text.
  • @LiuKang see my edits which make this case insensitive and check what I've written regarding "!" or "?".
  • Removed - and /, replaced with ? and !. Tested the following sentences I doge my BIG DOG and dog dogteeh. Dogs doglike a dogface. Dog,dog,dog.Dog? DOG! and it returned 7, which is exactly correct. You solved it!
  • Wrap the whole thing in =if(B1<>"",...) - replacing "..." with the formula above. Or, just change the last "+1?" if statement to: +IF(AND(B1<>"",RIGHT(C1,LEN(B1))=B1),1,0)
  • Replaced the commas , with semi-colon ;, but getting 0 in C1.
  • Did you confirm with CTRL + SHIFT + ENTER, or just ENTER? Did you type the punctuation into C1:C6? I have tested this so it should work if formatted correctly.
  • Thanks for your answer. Sorry, but I think it would not work so good. I am using this to analyze large portions of texts, some of them will be over 500 words (many columns with your solution) and it might be 100 rows of text. Plus I will analyse more than one word, maybe 10.
  • If the above works with one or two sentences, it can work with hundreds, especially if we use VBA to do the work.
  • Using this formula on potatos. potato potato I am still getting 3 and not 2.