SOLVED

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

Occasional Contributor

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

 1st column 2nd column matching a,b,c a,c,z a,c c,z.d c,z c,z d,e,e e,d,k d,e b,a,c a,b a,b a,b,c a,c,z a,c c,z.d c,z c,z d,e,e e,d,k d,e b,a,c a,b a,b a,b,c a,c,z a,c c,z.d c,z c,z d,e,e e,d,k d,e b,a,c a,b a,b
6 Replies

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

What output do you expect from your given sample? Can you make a 4th column showing your desired result?
best response confirmed by HZ2506 (Occasional Contributor)
Solution

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

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

An alternative could be this formula.

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

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

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

wow this formula work, thanks a lot, this really help.....

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

@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)))``

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

wow!, this formula also work, thanks a lot, really help.....