removing words/phrase from sentences

[Runt]

I'm looking to create a column of sentences where the words/phrases
which appear in Column A have been removed from the sentences which
appear in Column B so if...

Cell A1 = "take sg over"
Cell B1 = "They plan on taking the company over."

....then I want to get...

Cell C1 = "They are planning to ...... the company ......"

Do you think it is possible to do this using either a MACRO or a
FUNCTION?. I'm currently using the following function for Cell C1...

=REPLACE(B1,SEARCH(LEFT(A1,4),B1), SEARCH("
",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"."," "),"?"," "), "!","
"),SEARCH(LEFT(A1,4),B1)+LEN(A1))-SEARCH(LEFT(A1,4),B1),".....")

....but it gives...

Cell C1 = "They plan on ..... over."

....which is obviously inadequate.

Any ideas? Your help would be greatly appreciated.

Chris

 

[Frank Kabel]

Hi
don't think this is really possible in Excel. Esp. with changing tense,
etc.

 

[Runt]

Thanks for the reply, Frank. Isn't there a MatchAllWordForms feature
in Excel as in MS Word? Could this not be used to capture changing
tenses?

What about matching the first 3 or 4 letters of the word. Would this
capture the majority of cases where the tense was different?

Thanks again for help.

Chris

 

[Frank Kabel]

Hi
there's no MatchAllWordForm feature in Excel. Regarding your other
question: yes it probably will

 

[Runt]

If I have a field which contains sentences and some of the words in a
sentence has an asterix beside them, e.g.

"*Mary had *a little *Lamb"

....would it be possible to write a function that would produce a
sentence where the asterixed words are replace with a gap ("....."),
e.g.

"..... had ..... little ....."

If so, I'd really appreciate a pointer to how I might achieve this.

Thanks in advance for your time.

Chris

 

[Don Guillett]

Have a look in HELP index for SUBSTITUTE

 

[Dave Peterson]

I'd use a user defined function for this.

If that's ok:

Option Explicit
Function ReplaceStarWords(rng As Range, _
Optional WithString As String = ".....") As String

Dim StarPos As Long
Dim SpacePos As Long
Dim myStr As String

myStr = rng(1).Value

Do
StarPos = InStr(1, myStr, "*", vbTextCompare)
If StarPos = 0 Then
Exit Do
End If
SpacePos = InStr(StarPos, myStr, " ", vbTextCompare)
If SpacePos = 0 Then
SpacePos = Len(myStr) + 1
End If

myStr = Application.Substitute(myStr, _
Mid(myStr, StarPos, SpacePos - StarPos), WithString)

Loop

ReplaceStarWords = myStr

End Function

Then you could use that formula in a helper cell.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=======

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=ReplaceStarWords(a1)
You could even pass it a different string to use:
=replacestarwords(a1,"???")
if you want.

If you want to update the values in place, I'd use this little macro to change
all the Selected cells.

Sub testme()
Dim myCell As Range
For Each myCell In Selection.Cells
myCell.Value = ReplaceStarWords(myCell)
Next myCell
End Sub

 

[Runt]

Dave,

It works! Thank you. You've been a great help.

Cheers,

Chris