I have two columns/cells that look like this:

My question: How can I remove the "Word123" (value of Column A) inside Column B so that the value of Column B looks like this: Word1 Word12 Word1234

Note that in Column B there is always one space character betweens these values and I have a lot of rows that need this kind of processing.

I have tried using the SUBSTITUTE Function but that doesnt seem to do the trick

CodePudding user response：

Try the following formula-

=LET(x,TEXTSPLIT(B2,," "),TEXTJOIN(" ",1,FILTER(x,COUNTIFS(A2,x)=0)))

CodePudding user response：

SUBSTITUTE works fine too... But you need to use some extra spaces to separate the strings clearly:

=TRIM(SUBSTITUTE(" " & B1 & " " ; " " & A1 & " " ; " "))