Forum Discussion
How to highlight cells where there are 2 or more words the same (ie a name)
SGeorgie Highlight the cell range and select conditional formatting and select custom formula and use this:
=ROWS(TEXTSPLIT(A1,," "))>ROWS(UNIQUE(TEXTSPLIT(A1,," ")))where A1 is replaced with the upper left most cell of the applied to range
(note this assumes you have Excel 365)
mtarler This kind of works but highlights those that are not containing the same name
I want it the other way from please
Thanks
That doesn't seem right. What did you set the formatting to be? Are you sure the column isn't formatted a peach fill and the conditional formatting is changing it to white?
That all said there is a bigger issue here and that this formula looks for a duplication of ANY word in the text so Mr George and Mr Joe would be found true because "Mr" is repeated. The formula will get much more complicated if we have to exclude specific words like Mr, Mrs, Ms, and Miss and "and" itself if there might be a case of "Mr George and Mrs Sarah and Miss Sue" because the "and" is repeatedfor example something like this includes a list of words/terms to exclude:
=LET(in,A1, exclude,{"Mr","Mrs","Miss","Dr","Ms","Jr","Sr","and"}, wlist,TEXTSPLIT(in,," "), fwlist,FILTER(wlist,NOT(ISNUMBER(MATCH(wlist,exclude,0)))), ROWS(fwlist)>ROWS(UNIQUE(fwlist)))- ar i set this formula with peach but yeah lots will be Mr ... and Mr ....
while other are Miss. and Miss..... and Miss..............
no worries, thank you