Forum Discussion

AmyYang's avatar
AmyYang
Brass Contributor
Dec 28, 2021

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 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?

Thanks,

 

Amy  

excel

4 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 28, 2021

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

    • AmyYang's avatar
      AmyYang
      Brass Contributor
      Dec 30, 2021
      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.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Dec 30, 2021

        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.

Resources