Forum Discussion
Solved
Extract text from Comma Separated Cell to another Comma Separated Cell
Hi Community,
I was wondering if anyone could help me with this problem.
Any method to achieve "Desired Outcome" column would be appreciated.
| Data | Desired Outcome |
| BAR-Barcelona (1), RM-Real Madrid (2), MU-Manchester Utd (3), CHE-Chelsea (4), LIV-Liverpool (5) | BAR, RM, MU, CHE, LIV |
| BAR-Barcelona (1), RM-Real Madrid (2) | BAR, RM |
| MU-Manchester Utd (3), LIV-Liverpool (5) | MU, LIV |
| BAR-Barcelona (1) | BAR |
Many thanks
=LET( ζ, TEXTSPLIT(A1, "-", " "), TEXTJOIN(", ", , FILTER(TAKE(ζ, , 1), 1 - ISNA(TAKE(ζ, , -1)))) )
5 Replies
One more variant
=LET( range, $B$2:$B$5, MAP( range, LAMBDA(v, LET( s, TEXTSPLIT(v, {",","-"}), TEXTJOIN(", ", , CHOOSECOLS(s, SEQUENCE(COLUMNS(s) / 2, , 1, 2))) ) ) ) )As variant
=TEXTJOIN(", ",, FILTERXML("<t><s>"& SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(B2,"-","</s><s>"),",","</s><s>"), " ", "") &"</s></t>", "//s[string-length()<4]") )for
=LET( ζ, TEXTSPLIT(A1, "-", " "), TEXTJOIN(", ", , FILTER(TAKE(ζ, , 1), 1 - ISNA(TAKE(ζ, , -1)))) )- Thank you, this works smoothly
