SubscribeExcel 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.
Trackbacks & Pingbacks
- Pingback by Pages tagged "repeated" on November 11, 2008 @ 2:45 pm
- Pingback by Analyzing Search Keywords using Excel : Array Formulas in Real Life | Charts & Graphs | Pointy Haired Dilbert - Chandoo.org on April 29, 2009 @ 8:17 pm
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
Nice Chandoo!! So I can now do pattern matching and tracking!
Very useful!!! I had no idea it could be done… thank you!!
hi chandoo,
i got this below formulae on another site which picks all the text appearing after “/” in a cell….however i can’t understand how it works….specially if you could tell me what does this REPT function and 255 value does in this formulae…..
=TRIM(RIGHT(SUBSTITUTE(B23,”/”,REPT(” “,255)),255))
u can call me a novice wrt formulaes……
@Kida… yeah, you can do some basic pattern matching using this other text manipulation functions.
@Michelle: you are welcome
@Azmat: Thanks for sharing this formula with all. Even I didn’t know about this and it definitely goes in to my favorite formula tricks.
here is how it works:
- the formula produces portion of the cell contents after last “/” symbol.
- assuming, the last part is not longer than 255 characters (usually the case)
- It replaces every “/” with 255 spaces (the rept() function is doing that)
- Now, it uses right() to extract right most 255 characters. Which probably has a bunch of spaces and then the last portion and another bunch of space.
- Finally it uses trim to cut down spaces to provide just the string.
If your string has more than 255 characters after the last “/” symbol, you can use even larger number like 999 in rept() and right().
Is there a formula if I want to see if a word is repeated in a column?
@Glenn you can try countif()
assuming you want to see if word “Glenn” is repeated in the range C1:C20, write something like =IF(COUNTIF(C1:C20,”*Glenn*”)>1,”Repeated”,”Not Repeated”)