Forum Discussion
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?
Appreciate everyone advise. TQ.
| 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 |
=TEXTJOIN(",",,UNIQUE(FILTER(TRANSPOSE(TEXTSPLIT(B2,",")),TRANSPOSE(ISNUMBER(SEARCH(TEXTSPLIT(B2,","),A2))))))An alternative could be this formula.
6 Replies
=TEXTJOIN(",",,UNIQUE(FILTER(TRANSPOSE(TEXTSPLIT(B2,",")),TRANSPOSE(ISNUMBER(SEARCH(TEXTSPLIT(B2,","),A2))))))An alternative could be this formula.
- wow this formula work, thanks a lot, this really help.....
- What output do you expect from your given sample? Can you make a 4th column showing your desired result?
