How to Count Repeated Words in Excel

In this Excel tutorial lesson, you will learn some trick - how to count repeated words. I implemented a word counter and would like to share it with you.

Counting words in cell

There is a task. How to count repeated words in a cell?

For example, you have some words (e.g. aa) which repeat many times in one cell.

Counting Repeated Words in a Cell

How to count it?

Use that function:


Count Words in Cell

The Word Counter worked. There are 9 "aa" words. Counting Repeated Words in a Cell worked!

Counting words in column

To count the number of repeated words in Microsoft Excel, you can use a combination of the IF function and the COUNTIF function.

Here's an example:

In a separate column, write a formula to compare each cell in the column containing your words with all other cells in that same column:

=IF(COUNTIF(A:A, A1)>1, A1, "")

Drag the formula down to the last row of your data.

Filter the new column for non-empty cells, which will show only the repeated words.

Note that this method assumes that the words are in separate cells in the same column. If they are in a single cell, you'll need to use a different approach, such as using a macro or splitting the cell into separate cells before using the above method.