Remove list of keywords in Sentence

[goble]

Hello,

this has been asked before but my problem is a bit different. I want to get rid of some words in a column using a Macro (or formulae)

text	revised text	Text to remove
This is a sentence containing keywords. For Analysis, for general.	sentence,keywords,analysis,general	is
this is a General Analysis	general,analysis	this
		a
		containing
		for

Notes:

1. Revised text should not contain spaces i.e word1,word2
2. "This" (starting with capital letter) has been removed even though "this" lower case has been specified
3. Column "Text to remove" includes the list of predefined words that will need to be removed
4. "Analysis" has not (or part of it) been removed even though it includes "is"
5. "." and "," should be removed but result should be delimited with ","
6. Revised text should all be in lower case.

P.S I know I'm asking a lot ! But thanks in advance

 

[Peter_SSs]

Is this closer to the mark?

Function Remove(s As String, rRemove As Range, rSpecial As Range) As String
  Dim RX As Object, m As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  RX.Pattern = "\b(" & Join(Application.Transpose(rSpecial), "|") & ")\b"
  For Each m In RX.Execute(s)
    s = Replace(s, m, Replace(m, " ", "#"), 1, -1, 1)
  Next m
  RX.Pattern = "[^A-Za-z#0-9\-]"
  s = LCase(RX.Replace(s, " "))
  RX.Pattern = "\b(" & Join(Application.Transpose(rRemove), "|") & ")\b"
  Remove = Replace(Replace(Application.Trim(RX.Replace(s, "")), " ", ","), "#", " ")
End Function

goble.xlsm
	A	B	C	D
1	text	Text to remove	Special words
2	This is a sentence containing keywords. For Analysis, for general.	is	las vegas	sentence,keywords,analysis,general
3	this is a General Analysis	this	los angeles	general,analysis
4	4k desktop wallpaper	a	salt lake city	4k,desktop,wallpaper
5	state-of-the-art technique	containing		state-of-the-art,technique
6	Building in Las Vegas	for		building,las vegas
7	For Los Angeles and Las Vegas or in Salt Lake City	in		los angeles,and,las vegas,or,salt lake city
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=Remove(A2,B$2:B$7,C$2:C$4)

 

[goble]

That's incredible, thank you so much.

But I would have preferred getting them the 2 process separately. Let me tell you why.

The generated Tags have to be somewhat manually adjusted. For example:

text	result 1	result 2	Special Words	To replace by
Las Vegas Flower	las vegas,flower	las vegas,flowers	flower	flowers
Flowers in Los Angeles	flowers,los angeles	flowers,los angeles	tulip	flowers
Tulip in New York	tulip,new york	flowers,new york

Even though the texts are correct in column A (singular, plural and a kind of flower). There shouldn't be a Tag for "flowers", one for "flower" another one for "tulip". This is where the "Replace by" Column become handy and a human intervention is needed there.

Actually when someone click on flowers, he will get a list of pages which are tagged under flowers. This is done to prevent generating too many tags on the website.

 

[Peter_SSs]

But I would have preferred getting them the 2 process separately.

Perhaps like this?

Function Remv(s As String, rkeys As Range) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[^A-Za-z0-9'\-]"
    Remv = LCase(.Replace(s, " "))
    .Pattern = "\b(" & Join(Application.Transpose(rkeys), "|") & ")\b"
    Remv = Replace(Application.Trim(.Replace(Remv, "")), " ", ",")
  End With
End Function

Function Repl(s As String, rRepl As Range) As String
  Dim a As Variant
  Dim i As Long
  Dim tmp As String
  
  a = rRepl.Value
  tmp = "," & s & ","
  For i = 1 To UBound(a)
    tmp = Replace(tmp, "," & a(i, 1) & ",", "," & a(i, 2) & ",")
  Next i
  Repl = Mid(tmp, 2, Len(tmp) - 2)
End Function

goble.xlsm
	A	B	C	D	E	F
1	text	Text to remove	result 1	Special Words	To replace by	result 2
2	This is a sentence containing keywords. For Analysis, for general.	is	sentence,keywords,analysis,general	las,vegas	las vegas	sentence,keywords,analysis,general
3	this is a General Analysis	this	general,analysis	los,angeles	los angeles	general,analysis
4	4k desktop wallpaper	a	4k,desktop,wallpaper	salt,lake,city	salt lake city	4k,desktop,wallpapers
5	state-of-the-art technique	containing	state-of-the-art,technique	wallpaper	wallpapers	state-of-the-art,technique
6	Building in Las Vegas	for	building,las,vegas			building,las vegas
7	For Los Angeles and Las Vegas or in Salt Lake City	in	los,angeles,and,las,vegas,or,salt,lake,city			los angeles,and,las vegas,or,salt lake city
8	This is John-Paul O'Connor's analysis!		john-paul,o'connor's,analysis			john-paul,o'connor's,analysis
9	What is this? Is it a 6?		what,it,6			what,it,6
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=Remv(A2,B$2:B$7)
F2:F9	F2	=Repl(C2,D$2:E$5)

 

[goble]

Beautiful ! Thank you !

I'm incredibly amazed by the level of support you provide ! Again thank you so much !

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.