This is untested. It works with your example. In B1 array enter this formula and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
Formula:
=INDEX(MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1),MATCH(1,1/ISERROR(MID($A$1:$A$2,ROW(INDIRECT("1:"&LEN($A$1:$A$2))),1)),0))
In C1 this non-array formula and fill down.
Formula:
=SUBSTITUTE($A1,$B1,"")
For the comma separated strings array enter this in F1 and fill down.
Formula:
=INDEX(MID($E1,(ROW(INDIRECT("1:"&LEN($E1)))-1)*3+1,1),MATCH(1,1/ISERROR(MID($E$1:$E$2,(ROW(INDIRECT("1:"&LEN($E$1:$E$2)))-1)*3+1,1)),0))
then in G1 this non-array formula and filled down.
Formula:
=SUBSTITUTE(SUBSTITUTE($E1,$F1,""),", ","")
|
A |
B |
C |
D |
E |
F |
G |
1 |
XYZ |
Z |
XY |
|
X, Y, Z |
Z |
XY |
2 |
XYR |
R |
XY |
|
X, Y, R |
R |
XY |
3 |
|
|
|
|
|
|
|
Bookmarks