How to Count a Word in Excel on PC or Mac

Last Updated: March 12, 2018

This wikiHow teaches you how to count how to use Microsoft Excel to find out how many times a word appears in a range, as well as how to count the number of words in a single cell.

Method 1

Method 1 of 2:
Counting Instances of a Word in a Range

1
Open Excel on your computer. It’s in the All Apps area of the Start
menu in Windows, or the Applications folder in macOS.
2
Open the workbook you want to use. Press Control+O, select the file you want to access, then click Open.
3
Click an empty cell. This is where you’ll enter the formula.
4
Type the formula for counting the number of times a word occurs in a range. The format is =SUMPRODUCT(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"apple","")))/LEN("apple"). You’ll need to make the following modifications:
- Replace both instances of “A2:A7” with the range you want to evaluate.
- Replace both instances of “apple” with the word you’re counting.
5
Press ↵ Enter or ⏎ Return. The number of times the word shows up in this range now appears in the cell.

Method 2

Method 2 of 2:
Counting Words in a Cell

1
Open Excel on your computer. It’s in the All Apps area of the Start
menu in Windows, or the Applications folder in macOS.
2
Open the workbook you want to use. Press Control+O, select the file you want to access, then click Open.
3
Click an empty cell. This is where you’ll enter the formula.
4
Type the formula for counting the number of words in a cell. The format is =IF(LEN(TRIM(cell))=0,0,LEN(TRIM(cell))-LEN(SUBSTITUTE(cell," ",""))+1). You’ll need to make the following modification:
- Replace all three instances of “cell” with the cell number you’re counting.
- For example, if you’re counting the words in A2, replace “cell” with A2 in the formula.
5
Press ↵ Enter or ⏎ Return. The number of words in the selected cell now appears in place of the formula.