Forum Discussion

Derek275's avatar
Derek275
Copper Contributor
Aug 14, 2020
Solved

How to filter whether a cell is contained in other cells?

Hi There, 

 

I am sorting out more than one thousand phrase information. Since the information is obtained in batches, I have to filter them to ensure that they are unique. They are not repeated, but some phrases are contained in other phrases. Let me give you an example, 

NO. 1  is  safety signs

NO. 2  is  emergency safety signs 

NO. 3  is  emergency signs

You can see the No.2 is included No.1 and No.3, so we need to find them (no1 and no 3 ) out and delete them.

 

So what kind of function should I choose for filtering, please? This is really important to me, I look forward to your early reply. 

 

Tks in advance. 

 

Derek

  • Derek275 

     

    I think you could try Countif with wildcards at the beginning, end, and between words. Say the descriptions are in B2:B4, then

     

    =COUNTIF(B$2:B$4,"*"&SUBSTITUTE(B2," ","*")&"*")

     

    and filter or look for items >1. Of course, make sure your workbook/data is backed up.

3 Replies

  • JMB17's avatar
    JMB17
    Bronze Contributor

    Derek275 

     

    I think you could try Countif with wildcards at the beginning, end, and between words. Say the descriptions are in B2:B4, then

     

    =COUNTIF(B$2:B$4,"*"&SUBSTITUTE(B2," ","*")&"*")

     

    and filter or look for items >1. Of course, make sure your workbook/data is backed up.

    • Derek275's avatar
      Derek275
      Copper Contributor

      can't believe, it works. Tks so much sir.  Have a good day! JMB17 

      • JMB17's avatar
        JMB17
        Bronze Contributor
        You're welcome and have a good day as well.

Resources