Forum Discussion

HZ2506's avatar
HZ2506
Copper Contributor
Nov 11, 2022
Solved

Extract multiple matching texts in separate in 2 columns (with multiple texts separate by comma)

Hi,   I have sample data as below, in the 3rd column would want to filter out all text that matching between 1st and 2nd column. Is there any formula function that can help to achieve this?   App...
BI & Data Analysis
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Nov 11, 2022

    HZ2506 

    =TEXTJOIN(",",,UNIQUE(FILTER(TRANSPOSE(TEXTSPLIT(B2,",")),TRANSPOSE(ISNUMBER(SEARCH(TEXTSPLIT(B2,","),A2))))))

    An alternative could be this formula.

     

Harun24HR's avatar
Harun24HR
Bronze Contributor
Nov 11, 2022
What output do you expect from your given sample? Can you make a 4th column showing your desired result?
  • HZ2506's avatar
    HZ2506
    Copper Contributor
    Nov 14, 2022

    Harun24HRHi, column no. 3 is the desire output that i expect....thank you.

    • Harun24HR's avatar
      Harun24HR
      Bronze Contributor
      Nov 14, 2022

      HZ2506 Oh! That's okay. You may try below formula. Check the attached file.

      =LET(x,TOCOL(TEXTSPLIT(A2,{",","."})),y,COUNTIFS(B2,"*"& x &"*"),z,FILTER(x,y),TEXTJOIN(",",TRUE,UNIQUE(z)))

       

       

      • HZ2506's avatar
        HZ2506
        Copper Contributor
        Nov 14, 2022
        wow!, this formula also work, thanks a lot, really help.....

Resources