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

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

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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

"John Svendsen" wrote:

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" wrote in message
...
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" wrote in message
...
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

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

"John Svendsen" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

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

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


Mike H wrote:

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

"John Svendsen" wrote:

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" wrote in message
...
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" wrote in message
...
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









--

Dave Peterson

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


"Mike H" wrote in message
...
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

"John Svendsen" wrote:

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" wrote in message
...
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" wrote in message
...
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

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

"Mike H" wrote in message
...
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

"John Svendsen" wrote:

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" wrote in message
...
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" wrote in message
...
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

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

"LEO@KCC" wrote in message
...
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

"John Svendsen" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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