Find & Replace Whole Words

[John Svendsen]

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

 

[Guest]

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

 

[LEO@KCC]

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

 

[John Svendsen]

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

 

[Guest]

Hi,

If I type in a cell the sentence:-

"Often these days I suffer elderly moments that manifest themselves in
confusion"

And then do Edit|Replace elderly with blonde my sentence becomes:-

"Often these days I suffer blonde moments that manifest themselves in
confusion"

Is that not replacing a whole word in a string?

Mike

 

[LEO@KCC]

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 ActiveSheet.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

 

[Dave Peterson]

"On the first workday, they decided that they themselves could do the work."

And change "the" to "this", then only two words would change?????

 

[John Svendsen]

Hi Mike
In your example this works but think if this:
"Often these days I suffer one moment that manifest themselves as a trobone"
and I want to replace "one" for "A", I'd get
"Often these days I suffer A moment that manifest themselves as a trobA"
See what I mean? JS

 

[LEO@KCC]

Mike, your example does not have an instance of the word to be replaced as
part of a longer word.

If you had: "The elders are many, an elder is one." and you attempted to
replace just "elder" (not "elders" or any variations therof), you could not
do it with the built in feature.

Leo

 

[John Svendsen]

Hi LEO,
Lovely, this is a very good idea!!!
I'm playing around with this approach and I see two things: after the
replace something like a little square box gets added to the end if the
repalced string; second, for some reason this is VERY slow (on a large
spreadsheet, tens of minutes!). Any ideas on how to improve this?
Thanks, JS