How can I remove some text from an adjacent cell?

Question

excel

Friends, such a question, how can I remove part of the text from a cell that matches the value of the neighboring cell?


Here is an example, on the left there is text, and on the right it is also present in the text. How can I massively delete from the right column the text that is present in the left?

Answer 1

See the SUBSTITUTE \ SUBSTITUTE function.
The formula will accordingly (fill in any free column on the right, for example, in D1)
=SUBSTITUTE(C1;B1;"")
It will replace in the contents of C1 what is contained in B1 (if it is there) with an empty substring.
Since in your example there is a space before the "tail" to be deleted, it makes sense to remove it as well, there are two options:
=TRIM(SUBSTITUTE(C1;B1;""))
or
=SUBSTITUTE(C1;" "&B1;"")
drag the formula down to the end of the table in any of the many ways.
Select column D, copy, immediately paste as values. After that, column C, if it is no longer needed, can be deleted. Or copy the values ​​from D to C. If some cells refer to C, the second way is correct, if not, it doesn't matter.
LIMITATION OF THE METHOD!
Imagine the situation below: if the value in column B of your table can be different from the end of the value of cell C, the following can happen (see the second line of the example).

B              C                          D            
adiDCP1_PL     Жёлуди adiDCP1_PL          Жёлуди
adiDCP1_PL     Грибы adiDCP1_PL2          Грибы2
adiDCP1_PL     Червецы adiDCP1_PL         Червецы

The end of the second line differs from the value of the cell in B by the presence of an additional character "2" at the end. However, "adiDCP1_PL" will be removed from the string and the 2 will remain. If this or similar can be in your table, you will need to look for another way.