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.
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.
=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.
Leave a Reply
|Excel Links of the Week – All Downloads at One Place Edition||Eight ways to exploit COUNTIF() … and sumif() too [spreadcheats]|