Re: Find & Replace Whole Words

John, I had another thought. Why don't you Early-Bind Word, and use its
replace method?

I tested my code below and it worked. You may have to adapt it to your needs
(for example note that it only looks into constant values, not formulas).
Test it in a safe environment please...

'Requires a reference to Microsoft Word 11.0 Object Library
Sub ReplaceWholeWord()
Dim objWD As New Word.Document, rge As Range
For Each rge In Active***.Cells.SpecialCells(xlCellTypeConstants, 23)
objWD.Content = rge.Value
With objWD.Content.Find
.ClearFormatting
.Text = "aaa"
.Replacement.Text = "ZZ"
.MatchWholeWord = True
.Execute Replace:=wdReplaceAll
End With
rge.Value = objWD.Content
Next rge
Set objWD = Nothing
End Sub

Regards,
Leo Trapano

"John Svendsen" <jfs@xxxxxxxxxxxxx> wrote in message
news:%23KmpFuH9HHA.2004@xxxxxxxxxxxxxxxxxxxxxxx
Hi LEO and Mike, Thanks for your reply
Yes LEO, you are right, it is just as you described what I am after. I
have thought of what you proposed, but there are so many possibilities as
a definition of a 'whole word' - that is why I asked for help, to see if I
could find standard rules for defining WholeWords.
Thanks for your help
Tks, JS

"LEO@KCC" <leo.trapano@xxxxxxx> wrote in message
news:u$Jh3kH9HHA.4180@xxxxxxxxxxxxxxxxxxxxxxx
Hi John,

I do not think Excel has the functionality you require built-in. (Mike,
please note that John is talking about the replace "Whole Word" feature
of Word, not just a simple replace...). Excel can only replace part of a
string or a whole string, NOT a whole word within a string.

I am sure there are ways do this in VBA, but I cannot think of anything
short or straight forward at present.

How about this: When you specify the string to be searched, add a space
before and after the word, and the same for the replacing string. For
example in vba:
Cells.Replace " bbb ", " ZZ ", xlPart

This will work if you consider "a whole word" to be a word surrounded by
a single space in each side, but may not work for, say, words next to a
full stop or a comma. You can in that case customise the statement and
add all possible scenarios you can think of....

Cells.Replace " bbb ", " ZZ ", xlPart
Cells.Replace " bbb.", " ZZ.", xlPart
Cells.Replace ". bbb ", ". ZZ ", xlPart
etc.

Regards

Leo

"Mike H" <MikeH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FAC33B77-D539-4FF8-AC2E-1163F255D86E@xxxxxxxxxxxxxxxx
John,

Excel has the functionality built in, check out Edit|Replace.

If you want to do it with a macro then try this:-

Sub sonic()
findstring = InputBox("Enter what to find")
replacestring = InputBox("Enter what to replace it with")
Cells.Replace What:=findstring, Replacement:=replacestring
End Sub

Mike

"John Svendsen" wrote:

Hi All:
I need to find & replace Whole Words in Excel 2003 - Word has this
option,
but not Excel.
I've been looking for code to do this in VBA but all I find are canned
programs/Add-ins.
Can someone please give me an idea of how to do this in VBA for Excel
2003?
TIA, JS









.