Forum Discussion
AmyYang
Dec 28, 2021Copper Contributor
How to find duplicate that is only contained partially in a cell
Hi, I'm aware of the Conditional Formatting that highlights duplicate values, however, how can I have excel find the duplicates within a partial cell? For example, if I am trying to find du...
HansVogelaar
Dec 28, 2021MVP
If one cell contains "12345" and another contains "56789", should they be highlighted since both have a "5"?
Same question for "Excel" and "PowerPoint", since both have an "e".
- AmyYangDec 30, 2021Copper ContributorDear Hans, thanks so much for your reply and you bring up a really good point!
I agree that it wouldn't make sense if searching for "e" among Excel and Powerpoint words.
What if in one cell, it contains multiple words like "Excel, Powerpoint, Word" and I only want to search for the word "Excel" then? Because I am trying to search for a unique name (clinical trial protocol ID) which it would not duplicate with the other cells.- HansVogelaarDec 30, 2021MVP
I have attached a sample workbook with some dummy data, a helper column and a custom VBA function.
Select Conditional Formatting > Manage Rules... > Edit Rule to view the conditional formatting rule.
Press Alt+F11 to activate the Visual Basic Editor and to view the code of the function.
You'll have to allow macros when you open the workbook.
- AmyYangJan 02, 2022Copper ContributorThank you kindly Hans for your time to create this excel!