Find text and replace based on the values of the two columns.

[Slavio]

Hello friends,
I have a question about finding the word "pear" in a string
I need to write the result in column C ...
In column A there is fruit with "Pear"
Column B contains other fruit, but also "Pear"
If there is a pear in column B, then write the result without "Pear" in column C.
The formula works if there is a Pear in column B, but when I add more fruit, it doesn't work. The result should be cherry, grapes
Please how to edit the formula?

Formula in C1: =SUBSTITUTE(SUBSTITUTE(A1;B1&", ";"");B1;"")

duplicity (version 1).xlsb
	A	B	C
1	apple, pear, cherry, grapes	pear	apple, cherry, grapes
2	apple, pear, cherry, grapes	pear, apple, banana	apple, pear, cherry, grapes
Hárok6

Cell Formulas
Range	Formula
C1:C2	C1	=SUBSTITUTE(SUBSTITUTE(A1,B1&", ",""),B1,"")

 

[kweaver]

Why "The formula works if there is a Pear in column B, but when I add more fruit, it doesn't work. The result should be cherry, grapes"
is that the result rather than "apple, cherry, grapes" ?

image.xlsx
	A	B	C
1	apple, pear, cherry, grapes	pear	apple, cherry, grapes
2	apple, pear, cherry, grapes	pear, apple, banana	apple, cherry, grapes
3	orange, banana, tomato	banana	orange, banana, tomato
4	orange, banana, pear	pear, orange	orange, banana
Sheet1

Cell Formulas
Range	Formula
C1:C4	C1	=IF(ISERROR(FIND("pear",A1)),A1,SUBSTITUTE(SUBSTITUTE(A1,"pear, ",""),", pear",""))

 

[Slavio]

Why "The formula works if there is a Pear in column B, but when I add more fruit, it doesn't work. The result should be cherry, grapes"
is that the result rather than "apple, cherry, grapes" ?

image.xlsx
	A	B	C
1	apple, pear, cherry, grapes	pear	apple, cherry, grapes
2	apple, pear, cherry, grapes	pear, apple, banana	apple, cherry, grapes
3	orange, banana, tomato	banana	orange, banana, tomato
4	orange, banana, pear	pear, orange	orange, banana
Sheet1

Cell Formulas
Range	Formula
C1:C4	C1	=IF(ISERROR(FIND("pear",A1)),A1,SUBSTITUTE(SUBSTITUTE(A1,"pear, ",""),", pear",""))

Thanks kweaver,
I guess I wrote it wrong. Sorry.
The word "Pear" is already in Column B as well as "Apple"
Therefore, these two words should not be in column C.
The formula should look for words that match in columns A and B. These words, which you will find, will not be written in column C.
In column "C", the result will be "A" without duplicate words from column "B".
I hope I have written this more clearly now.
(Important !: The word "Pear" cannot be found in the formula, nor any other fruit, because it can easily be a vegetable or another word)
So if in column A there is an Apple and in column B a Pear, an Apple, then in column C there is a Pear
Similarly: In column A there is Apple, Cherry and in Column B there is Pear, Cherry and apple, then the result is in column C: Apple, Pear
The fruit will vary.

 

[Fluff]

How about

++Fluff.xlsm
	A	B	C	D
1	apple, pear, cherry, grapes	pear	apple, cherry, grapes	apple, cherry, grapes
2	apple, pear, cherry, grapes	pear, apple, banana	apple, pear, cherry, grapes	cherry, grapes
Data

Cell Formulas
Range	Formula
C1:C2	C1	=SUBSTITUTE(SUBSTITUTE(A1,B1&", ",""),B1,"")
D1:D2	D1	=LET(a,FILTERXML("<k><m>"&SUBSTITUTE(A1,",","</m><m>")&"</m></k>","//m"),TEXTJOIN(", ",,FILTER(a,ISNA(MATCH(a,INDEX(FILTERXML("<k><m>"&SUBSTITUTE(B1,",","</m><m>")&"</m></k>","//m"),),0)))))

Note, this will only work on a desktop PC.

 

[Slavio]

=LET(a,FILTERXML("<k><m>"&SUBSTITUTE(A1,",","</m><m>")&"</m></k>","//m"),TEXTJOIN(", ",,FILTER(a,ISNA(MATCH(a,INDEX(FILTERXML("<k><m>"&SUBSTITUTE(B1,",","</m><m>")&"</m></k>","//m"),),0)))))

Thanks Fluff

The result should be C=A-B
Did I make a mistake converting the formula?
Did I make a mistake while converting the formula?
It gave me the same result

duplicity (version 1).xlsb
	A	B	C	D
1	apple, pear, cherry, grapes, bannana	pear, bannana	apple, pear, cherry, grapes, bannana	apple, pear, cherry, grapes, bannana
2	apple, pear, cherry, grapes, bannana	pear, bannana	apple, cherry, grapes
3			C=A-B
Hárok6

Cell Formulas
Range	Formula
C1	C1	=SUBSTITUTE(SUBSTITUTE(A1,B1&"; ",""),B1,"")
D1	D1	=LET(a,FILTERXML("<k><m>"&SUBSTITUTE(A1,";","</m><m>")&"</m></k>","//m"),TEXTJOIN("; ",,FILTER(a,ISNA(MATCH(a,INDEX(FILTERXML("<k><m>"&SUBSTITUTE(B1,";","</m><m>")&"</m></k>","//m"),),0)))))

 

[Fluff]

Why have you changed the "," to ";"

 

[Slavio]

Yes, I have figured it out in the meantime.
Of course, I always change "," ";" but this time only in some places ...
thank you everyone!!!

duplicity-2.xlsm
	A	B	C	D
1	apple, pear, cherry, grapes, bannana	pear, bannana	apple, pear, cherry, grapes, bannana	apple, cherry, grapes
2	apple, pear, cherry, grapes, bannana	pear, bannana	apple, cherry, grapes
3			C=A-B
Hárok6

Cell Formulas
Range	Formula
C1	C1	=SUBSTITUTE(SUBSTITUTE(A1,B1&", ",""),B1,"")
D1	D1	=LET(a,FILTERXML("<k><m>"&SUBSTITUTE(A1,",","</m><m>")&"</m></k>","//m"),TEXTJOIN(", ",,FILTER(a,ISNA(MATCH(a,INDEX(FILTERXML("<k><m>"&SUBSTITUTE(B1,",","</m><m>")&"</m></k>","//m"),),0)))))

 

[Fluff]

Glad you sorted it & thanks for the feedback.