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

Add to Favorites
Author: | Edits: don

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.

bf022ea033c07a50afec05664291cc3a.png

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.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
Capitalize the First Letter of Every Word in a Cell
Macro: This macro will make the text of any selected cell in excel proper case. This means that t...
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
Macro: UDF to count the number of words in a cell or range with a user-specified delimiter. ...
Formulas to Remove First or Last Character from a Cell in Excel
Tutorial: Formulas that allow you to quickly and easily remove the first or last character from a ce...
Formula to Count Occurrence of Specific Characters in a Cell
Tutorial: Formula that counts the occurrences of a specific character in a cell in Excel. This tutor...
Formula to Get Value of Last Non-Empty Cell in Excel
Tutorial: Formulas that you can use to get the value of the last non-empty cell in a range in Excel....
Enter a Constant Value in a Defined Name in Excel - Text, Numbers, Formulas, Etc.
Tutorial: In Excel you can store values in Defined Names.  Often people use a Defined Name to refe...
Tutorial Details
Downloadable Files: Excel File
Similar Content
Capitalize the First Letter of Every Word in a Cell
Macro: This macro will make the text of any selected cell in excel proper case. This means that t...
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
Macro: UDF to count the number of words in a cell or range with a user-specified delimiter. ...
Formulas to Remove First or Last Character from a Cell in Excel
Tutorial: Formulas that allow you to quickly and easily remove the first or last character from a ce...
Excel Forum