Formula to Count Occurrences of a Word in a Cell or Range in Excel

Formula to count how many times a word appears in a single cell or an entire range in Excel.

Special: this formula is special because it accounts for words that are at the start of the cell and also at the end of the cell, including sentences that have a period or other non-alphanumeric character at the end of the cell.

Sections:

Count Occurrences of a Word in a Cell

Count Occurrences of a Word in a Range

Formula Explanation

Notes

Count Occurrences of a Word in a Cell

=(LEN(" " & IF(A2="","",IF(AND(IFERROR(CODE(RIGHT(A2,1)),"")>=48,IFERROR(CODE(RIGHT(A2,1)),"")<=57),A2,IF(OR(AND(IFERROR(CODE(RIGHT(A2,1)),"")>=65,IFERROR(CODE(RIGHT(A2,1)),"")<=90),AND(IFERROR(CODE(RIGHT(A2,1)),"")>=97,IFERROR(CODE(RIGHT(A2,1)),"")<=122)),A2,LEFT(A2,LEN(A2)-1)))) & " ")-LEN(SUBSTITUTE(LOWER(" " & IF(A2="","",IF(AND(IFERROR(CODE(RIGHT(A2,1)),"")>=48,IFERROR(CODE(RIGHT(A2,1)),"")<=57),A2,IF(OR(AND(IFERROR(CODE(RIGHT(A2,1)),"")>=65,IFERROR(CODE(RIGHT(A2,1)),"")<=90),AND(IFERROR(CODE(RIGHT(A2,1)),"")>=97,IFERROR(CODE(RIGHT(A2,1)),"")<=122)),A2,LEFT(A2,LEN(A2)-1)))) & " ")," " & B2 & " ","")))/LEN(" " & B2 & " ")

This counts how many times a word from cell B2 appears in cell A2. You only need to change these cell references to get this to work.

The value in cell B2 must be lower case.

How to Quickly Change the Formula to Work for You

Change cell A2 to the cell with the text.

Change cell B2 to the cell with the word for which you are searching.

Put this formula on its own sheet and then hit Ctrl+F and go to the Replace tab and put A2 into the Find what input and the desired cell reference into the Replace with input and hit Replace All. Then, do the same for the cell reference B2. Then, you can copy/paste the formula to the desired location.

(make sure to do this on a separate sheet so you don't change any other formulas in the worksheet)

Count Occurrences of a Word in a Range

Array Formula

=SUM((LEN(" " & IF(A2:A10="","",IF(AND(IFERROR(CODE(RIGHT(A2:A10,1)),"")>=48,IFERROR(CODE(RIGHT(A2:A10,1)),"")<=57),A2:A10,IF(OR(AND(IFERROR(CODE(RIGHT(A2:A10,1)),"")>=65,IFERROR(CODE(RIGHT(A2:A10,1)),"")<=90),AND(IFERROR(CODE(RIGHT(A2:A10,1)),"")>=97,IFERROR(CODE(RIGHT(A2:A10,1)),"")<=122)),A2:A10,LEFT(A2:A10,LEN(A2:A10)-1)))) & " ")-LEN(SUBSTITUTE(LOWER(" " & IF(A2:A10="","",IF(AND(IFERROR(CODE(RIGHT(A2:A10,1)),"")>=48,IFERROR(CODE(RIGHT(A2:A10,1)),"")<=57),A2:A10,IF(OR(AND(IFERROR(CODE(RIGHT(A2:A10,1)),"")>=65,IFERROR(CODE(RIGHT(A2:A10,1)),"")<=90),AND(IFERROR(CODE(RIGHT(A2:A10,1)),"")>=97,IFERROR(CODE(RIGHT(A2:A10,1)),"")<=122)),A2:A10,LEFT(A2:A10,LEN(A2:A10)-1)))) & " ")," " & B2 & " ",""))))/LEN(" " & B2 & " ")

This counts how many times a word from cell B2 appears in the range A2:A10. You only need to change these cell references to get this to work.

Note: This is an Array Formula and that means that you need to enter it into the cell using Ctrl + Shift + Enter instead of just hitting the Enter key.

The value in cell B2 must be lower case.

How to Quickly Change the Formula to Work for You

Change cell A2:A10 to the range with the text.

Change cell B2 to the cell with the word for which you are searching.

Put this formula on its own sheet and then hit Ctrl+F and go to the Replace tab and put A2:A10 into the Find what input and the desired range reference into the Replace with input and hit Replace All. Then, do the same for the cell reference B2. Then, you can copy/paste the formula to the desired location.

(make sure to do this on a separate sheet so you don't change any other formulas in the worksheet)

Formula Explanation

These two formulas are obviously complex and, to be honest, you don't really need to learn everything, but, in this section, I'll break the formula down into a few separate parts to make it easier to understand.

Basic Formula

=(LEN(" " & A2 & " ")-LEN(SUBSTITUTE(LOWER(" " & A2 & " ")," " & B2 & " ","")))/LEN(" " & B2 & " ")

This counts the words in cell A2.

The words are counted by by taking the length of the cell and then subtracting the length of that cell after the desired word has been removed; you will then get the difference between those two numbers and divide them by the number of characters in the desired word, which is in cell B2 in this case. This is the process that gets the word count for the cell.

Extra spaces are added around the contents of the cell and also the word for which you are searching so that only full words will be counted and so that words at the start and the end of the sentence can be counted.

Formula to Remove Non-Alphanumeric Characters from the End of a Sentence

Most sentences end with a period or a semi-colon or an exclamation point or some similar symbol and this causes issues for our formula, so we need to remove those characters.

The below formula takes the value from cell A2 and removes the last character of it if it is not a letter or number.

=IF(A2="","",IF(AND(IFERROR(CODE(RIGHT(A2,1)),"")>=48,IFERROR(CODE(RIGHT(A2,1)),"")<=57),A2,IF(OR(AND(IFERROR(CODE(RIGHT(A2,1)),"")>=65,IFERROR(CODE(RIGHT(A2,1)),"")<=90),AND(IFERROR(CODE(RIGHT(A2,1)),"")>=97,IFERROR(CODE(RIGHT(A2,1)),"")<=122)),A2,LEFT(A2,LEN(A2)-1))))

This uses the CODE() function to check if a character is a letter or number.

The last character of the cell is fed to the CODE() function by using the RIGHT() function.

The IFERROR() function is used to account for errors caused by the CODE() function that occur when it is run on an empty cell - this is important for the array formula that works on an entire range. Technically, this doesn't need to be left in the formula for the one that works on a single cell, but it makes it easier to maintain when these two formulas are kept more similar.

The IF() function at the start accounts for an empty text value in cell A2 for the basic formula that works on only one cell.

Make it Work on Ranges

To make the formula work on a range of cells, we add one small function and turn it into an Array Formula, which means we input it into the cell using Ctrl + Shift + Enter instead of just Enter.

The SUM() function is added around the top part of the formula like this:

SUM((LEN(" " & IF(A2:A10="","",IF(AND(IFERROR(CODE(RIGHT(A2:A10,1)),"")>=48,IFERROR(CODE(RIGHT(A2:A10,1)),"")<=57),A2:A10,IF(OR(AND(IFERROR(CODE(RIGHT(A2:A10,1)),"")>=65,IFERROR(CODE(RIGHT(A2:A10,1)),"")<=90),AND(IFERROR(CODE(RIGHT(A2:A10,1)),"")>=97,IFERROR(CODE(RIGHT(A2:A10,1)),"")<=122)),A2:A10,LEFT(A2:A10,LEN(A2:A10)-1)))) & " ")-LEN(SUBSTITUTE(LOWER(" " & IF(A2:A10="","",IF(AND(IFERROR(CODE(RIGHT(A2:A10,1)),"")>=48,IFERROR(CODE(RIGHT(A2:A10,1)),"")<=57),A2:A10,IF(OR(AND(IFERROR(CODE(RIGHT(A2:A10,1)),"")>=65,IFERROR(CODE(RIGHT(A2:A10,1)),"")<=90),AND(IFERROR(CODE(RIGHT(A2:A10,1)),"")>=97,IFERROR(CODE(RIGHT(A2:A10,1)),"")<=122)),A2:A10,LEFT(A2:A10,LEN(A2:A10)-1)))) & " ")," " & B2 & " ",""))))

Then this is divided by the same thing as the previous formula:

/LEN(" " & B2 & " ")

This array formula looks a lot longer because the cell reference A2 was updated to A2:A10, which added more characters.

Notes

These formulas are COMPLEX. I recommend keeping this tutorial handy and also saving these formulas somewhere safe so you can refer back to them in the future and merely change the cell references to suit your needs.

The value in cell B2 must be lower case.

Make sure to download the attached workbook so you can see these formulas in action in Excel.