I thought there was a - match whole word - option with the Find and Replace
tool but I don't see it now. I'm trying to replace every "and" with "&" but
not affect any words that contain "and" such as "hand" or "bandit".

Be sure to type a single space before and after the and
--
Gary''s Student - gsnu200764


"Lyn" wrote:

I thought there was a - match whole word - option with the Find and Replace
tool but I don't see it now. I'm trying to replace every "and" with "&" but
not affect any words that contain "and" such as "hand" or "bandit".

Thanks so much! That did it!

"Gary''s Student" wrote:

Be sure to type a single space before and after the and
--
Gary''s Student - gsnu200764


"Lyn" wrote:

I thought there was a - match whole word - option with the Find and Replace
tool but I don't see it now. I'm trying to replace every "and" with "&" but
not affect any words that contain "and" such as "hand" or "bandit".

I'm not aware of a way to do that, someone here might enlighten us. What i do
know is that you have an and as a word it will have spaces on both sides so
you can use " and " to get all those. Be warned that anything that starts
with and or has punctuation after it will not get changed.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Lyn" wrote:

I thought there was a - match whole word - option with the Find and Replace
tool but I don't see it now. I'm trying to replace every "and" with "&" but
not affect any words that contain "and" such as "hand" or "bandit".

On Thu, 10 Jan 2008 06:36:01 -0800, Lyn wrote:

I thought there was a - match whole word - option with the Find and Replace
tool but I don't see it now. I'm trying to replace every "and" with "&" but
not affect any words that contain "and" such as "hand" or "bandit".

If you do not want to change "and" when it is at the beginning or end of the
string, then

Find what: <spaceand<space
Replace with: <space&<space

If you also want to replace "and" when it occurs at the beginning and end of
the line, then you can use this macro on a cell selection:

==============================
Option Explicit
Sub reReplace()
Dim c As Range
Dim re As Object
'This pattern will replace all "and" including
' those at beginning and end of string
'To omit those at the beginning and end
'Const sPat As String = "\sand\s"
'Const sRepl As String = " & "
Const sPat As String = "\band\b"
Const sRepl As String = "&"

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True
re.ignorecase = True
For Each c In Selection
c.Value = re.Replace(c.Value, sRepl)
Next c

End Sub
===================================
--ron