This procedure uses a straightforward algorithm that searches for repeated words within cells. If a cell contains words that repeat, it returns TRUE. If no words are repeated, it returns FALSE. Only exact matches of entire words are considered repetitions.
What counts as a “word” for this macro? Actually, it is a sequence of characters
- separated by spaces in the middle of a string;
- at the end of a string, preceded by a space;
- at the beginning of a string, followed by a space.
It’s recommended to search for cells with repeating words before deleting word repetitions, so you can visually confirm which phrases contain multiple occurrences of the same word.
When processing a keyword list or text dataset, this operation is often useful to run together with lemmatization to remove repetitions of different word forms of the same term.
Run it via the menu “Find -> words → Repeating Words” in the FIND group.
Find the Most Repeated Words in Text
If your task is not only to find repetitions but also to identify the most frequently repeated words across an entire column of text, you’ll need to run frequency analysis — also available in Excel with !SEMTools. Since this feature is especially popular with PPC and SEO professionals, it’s located in the relevant tool group. Below is an example showing the most common words in the keywords from this article.
Read more: word frequency analysis in Excel
See also:
Delete repeated words in Excel
This post is also available in RU.