Excel Formula for Finding Repeated Words in a Cell

If you ever need to know whether a particular word is repeated in a cell (for eg: a file extension or telephone number or name of the customer) here is how you can find it.

=if(lower(cell-with-text)<>SUBSTITUTE(lower(cell-with-text),lower(word-to-check),"",2),"Yes","No")

We are using substitute() to replace the second occurance of a particular word and then compare this with original cell contents. If both are not equal, we know that the word is repeated.

For eg: =if(lower("all cats are dogs, but not all dogs are cats")<>SUBSTITUTE(lower("all cats are dogs, but not all dogs are cats"),lower("cat"),"",2),"Yes","No") would return “yes”

See related posts for more text processing using excel hacks.