Search Cell Against Range and Remove Any Matching Text

[mdsurf]

Hi! I'm trying to figure out how to remove text from a cell if it is present in a range. For example Let's say I have a cell that contains the following sentence.

John Smith left his home and went to the store on Saturday to buy groceries

Now let's say I also have a column with a list of values.

Range of Values

John Smith

Saturday

groceries

fishing

basketball

Is any of the text from the range of values (exact match) is present in the sentence, is there a way to remove it?

If set up properly the equation should return the value.

left his home and went to the store on to buy

In the answer above anything exactly matching the range of values has been removed. I know it's a bit of a weird example but trying my best to illustrate what I need to accomplish.

Thank you!

 

[JEC]

When your range of Values is starting in D2.
If you want you can also have this as a Sub, changing the existing data

Function jec(cell As String) As String
 Dim RXPattern
 RXPattern = Join(Application.Transpose(Range("D2", Range("D" & Rows.Count).End(xlUp))), "|")
 
 With CreateObject("VBScript.RegExp")
   .Global = True
   .Pattern = RXPattern
   jec = Application.trim(.Replace(cell, ""))
 End With
End Function

=jec(A1)

Map1
	A	B	C	D
1	John Smith left his home and went to the store on Saturday to buy groceries			Range of Values
2	left his home and went to the store on to buy			John Smith
3				Saturday
4				groceries
5				fishing
6				basketball
KPI data dkdfdf

Cell Formulas
Range	Formula
A2	A2	=jec(A1)

 

[mdsurf]

Hi JEC, thank you so much for the help here. Do you know if this is possible to do without VBA? I'm pretty newbish to excel and VBA is a bit over my head, wondering if there's an equation that can accomplish this as well.

Either way, thank you for your time and help.

 

[Fluff]

What version of Excel are you using & on what platform?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[JEC]

In this case you need VBA..unfortunately there isn't a clean way to do this with formula's

 

[Fluff]

unfortunately there isn't a clean way to do this with formula's

That just depends on the OP's version of xl, but he/she doesn't seem to want to tell us.

 

[JEC]

What if the list of values to replace is like 50 items long, how are you going to do that with a dynamic formula, all at once

 

[Fluff]

As I said, just depends on the version. But I'm not going to waste my time if the OP cannot be bothered to let us know what version they are using & I have asked for that info in two different threads.

 

[JEC]

I am actually curious too

 

[JEC]

One option

Map1
	A	B	C	D
1	John Smith left his home and went to the store on Saturday to buy groceries			Range of Values
2	left his home and went to the store on to buy			John Smith
3				Saturday
4				groceries
5				fishing
6				basketball
Blad1

Cell Formulas
Range	Formula
A2	A2	=LET(x,FILTERXML("<x><y>"&SUBSTITUTE(A1," ","</y><y>")&"</y></x>","//y"),TEXTJOIN(" ",1,FILTER(x,ISERROR(MATCH(x,FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(" ",1,D2:D6)," ","</y><y>")&"</y></x>","//y"),0)),"")))