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.

Count Occurrences of a Word in a Cell

Count Occurrences of a Word in a Range

`=(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.

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)

**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.

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)

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.

`=(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.

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.

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.

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.

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...

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. ...

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...

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...

Tutorial: Formula that counts the occurrences of a specific character in a cell in Excel. This tutor...

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: In Excel you can store values in Defined Names. Often people use a Defined Name to refe...

Run a Macro when a User Does Something in the Worksheet in Excel

Tutorial: How to run a macro when a user does something in the worksheet, everything from selecting ...

Tutorial: How to run a macro when a user does something in the worksheet, everything from selecting ...