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.
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Excel Links of the Week – All Downloads at One Place Edition||Eight ways to exploit COUNTIF() … and sumif() too [spreadcheats] »|