Using VBA to remove sentences containing specific words

[Syringer]

Say I have a paragraph and I want to remove the one sentence in there with the phrase "ship" in it, and leave everything else in tact.

For example if I have the paragraph:

I like apples. And bananas. Free Shipping offered. I love cookies.

It would end up as:

I like apples. And bananas. I love cookies.

The way I imagine it working is using "instr" to find where "ship" is, and then finding the positions of the period immediately before and after it..then combining the sentences before and after it using LEFT and RIGHT functions.

So far I have:

public function newdesc(desc)
Dim p1 As Integer //p1 = position of period before "ship"
Dim p2 As Integer //p2 = position of period after "ship"

pos_ship = InStr(desc, "ship") //position of "ship"

If pos_ship > 0 Then
p2 = InStr(pos_ship, desc, ".")

Then I would end up with:

newdesc = left(desc, p1) & right(desc, len(desc) - p2)

But how do I define "p1"?

 

[PhatoseAlpha]

I'm pretty sure VBA has a split function. Should just be a matter of splitting the string, going through the array, and adding ones that don't contain your keyword into another string.

Of course, this assumes you only have statements, no questions or exclamations.

 

[Syringer]

Ahh yes didn't know about "split", just looked it up and it works perfectly.. thanks!

 

[Syringer]

Public Function desc2(desc)

d = Split(desc, ".")

For i = 0 To UBound(d)
If InStr(d(i), "ship") = 0 Then
desc2 = desc2 & d(i) & ". "
End If
Next i

End Function

works like a charm!