How to find duplicate that is only contained partially in a cell

New Contributor

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 duplicate value "12345", I know excel can find this if another cell says "12345", but I want excel to find this from this third cell "ABC 12345 XYZ". So excel needs to search for this partial match. 

 

How can this be done?

Excel - Find partial duplicate.jpg

Thanks,

Amy  

4 Replies

@AmyYang 

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".

Dear 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.

@AmyYang 

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.

Thank you kindly Hans for your time to create this excel!