Formula to edit part of a text in one cell based on an values in another cell

Question

I'm looking for an excel based formula for one of my projects which can help in removing certain parts of a cell (Cell A - names separated by a based on the values in Cell B (similar format as Cell A). I'm running a flow on this file as well hence cannot run VBA based solutions as my connector is excel online.

Example

Cell A - cat;dog;rat;rabbit

Cell B - rabbit;dog

Result needs to be - cat;rat

Jos Woolley · Answer 1 · 2022-07-17T07:22:59.837

0

For O365 Beta Channel:

=TEXTJOIN(";",,UNIQUE(TEXTSPLIT(A1&";"&B1,,";"),,1))

All O365 users (though works only in the desktop version, not online):

=TEXTJOIN(";",,UNIQUE(FILTERXML("<a><b>"&SUBSTITUTE(A1&";"&B1,";","</b><b>")&"</b></a>","//b"),,1))

For Excel Online:

=TEXTJOIN(";",,UNIQUE(TRIM(MID(SUBSTITUTE(A1&";"&B1,";",REPT(" ",99)),99*SEQUENCE(1+LEN(A1&";"&B1)-LEN(SUBSTITUTE(A1&";"&B1,";","")),,0)+1,99)),,1))

edited Jul 17 '22 at 07:22

answered Jul 17 '22 at 05:13

Jos Woolley

8,564
2
4
9

Thank you for your input Jos. Is there any alternative for excel online? – Githin Mathew George Jul 17 '22 at 07:11
@GithinMathewGeorge Added an option for Excel Online – Jos Woolley Jul 17 '22 at 07:23
This alternative works although it still gives me an error while applying to my mailing list. An extra semi-colon (;) gets added every 102 characters. Any solution for this issue? – Githin Mathew George Jul 17 '22 at 07:40
Given that it works for the dataset you posted, suggest you post some more realisitic data. – Jos Woolley Jul 17 '22 at 07:45

Formula to edit part of a text in one cell based on an values in another cell

1 Answers1