How to Count a Word in Excel on PC or Mac

TellChell

Editor

posted on 7 years ago — updated on 1 second ago

2
views

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.

Steps

Counting Instances of a Word in a Range

Open Excel on your computer. It’s in the All Apps area of the Start Windows Start menu in Windows, or the Applications folder in macOS.

Open the workbook you want to use. Press Control+O, select the file you want to access, then click Open.

Click an empty cell. This is where you’ll enter the formula.

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.

Press ↵ Enter or ⏎ Return. The number of times the word shows up in this range now appears in the cell.

Counting Words in a Cell

Open Excel on your computer. It’s in the All Apps area of the Start Windows Start menu in Windows, or the Applications folder in macOS.

Open the workbook you want to use. Press Control+O, select the file you want to access, then click Open.

Click an empty cell. This is where you’ll enter the formula.

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.