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

Slavio

Board Regular
Joined
Mar 28, 2021
Messages
59
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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
ABC
1apple, pear, cherry, grapespearapple, cherry, grapes
2apple, pear, cherry, grapespear, apple, bananaapple, pear, cherry, grapes
Hárok6
Cell Formulas
RangeFormula
C1:C2C1=SUBSTITUTE(SUBSTITUTE(A1,B1&", ",""),B1,"")
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
ABC
1apple, pear, cherry, grapespearapple, cherry, grapes
2apple, pear, cherry, grapespear, apple, bananaapple, cherry, grapes
3orange, banana, tomatobananaorange, banana, tomato
4orange, banana, pearpear, orangeorange, banana
Sheet1
Cell Formulas
RangeFormula
C1:C4C1=IF(ISERROR(FIND("pear",A1)),A1,SUBSTITUTE(SUBSTITUTE(A1,"pear, ",""),", pear",""))
 
Last edited:
Upvote 0
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
ABC
1apple, pear, cherry, grapespearapple, cherry, grapes
2apple, pear, cherry, grapespear, apple, bananaapple, cherry, grapes
3orange, banana, tomatobananaorange, banana, tomato
4orange, banana, pearpear, orangeorange, banana
Sheet1
Cell Formulas
RangeFormula
C1:C4C1=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.
 
Upvote 0
How about
++Fluff.xlsm
ABCD
1apple, pear, cherry, grapespearapple, cherry, grapesapple, cherry, grapes
2apple, pear, cherry, grapespear, apple, bananaapple, pear, cherry, grapescherry, grapes
Data
Cell Formulas
RangeFormula
C1:C2C1=SUBSTITUTE(SUBSTITUTE(A1,B1&", ",""),B1,"")
D1:D2D1=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.
 
Upvote 0
Solution
=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
ABCD
1apple, pear, cherry, grapes, bannanapear, bannanaapple, pear, cherry, grapes, bannanaapple, pear, cherry, grapes, bannana
2apple, pear, cherry, grapes, bannanapear, bannanaapple, cherry, grapes
3C=A-B
Hárok6
Cell Formulas
RangeFormula
C1C1=SUBSTITUTE(SUBSTITUTE(A1,B1&"; ",""),B1,"")
D1D1=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)))))
 
Upvote 0
Why have you changed the "," to ";"
 
Upvote 0
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
ABCD
1apple, pear, cherry, grapes, bannanapear, bannanaapple, pear, cherry, grapes, bannanaapple, cherry, grapes
2apple, pear, cherry, grapes, bannanapear, bannanaapple, cherry, grapes
3C=A-B
Hárok6
Cell Formulas
RangeFormula
C1C1=SUBSTITUTE(SUBSTITUTE(A1,B1&", ",""),B1,"")
D1D1=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)))))
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top